﻿--================================================================================================
--                                 Change History
--                                 --------------
-- Date          Who     Description
-- -----------   ------  --------------------------------------------------
-- 19/Jan/2024   AndyG   Added Delta flags 13-18
--
--New Fields:
--  StudentFlagValue13-18
--Changed SPs:
--  sp_ImportHoldingTable_DeltaStudentInformationFlag_MakeLive
--  sp_WebPlus_Delta_EnrolmentInfo_Select
--  sp_WebPlus_Delta_StudentScores_Report_Select
--  sp_WebPlus_Delta_StudentScores_Select
-- ===================================================================================================================

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Delta_Student_InformationFlag' AND COLUMN_NAME = 'StudentFlagValue13')
BEGIN
	ALTER TABLE Delta_Student_InformationFlag
	ADD StudentFlagValue13 VARCHAR(20) NULL
END

GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Delta_Student_InformationFlag' AND COLUMN_NAME = 'StudentFlagValue14')
BEGIN
	ALTER TABLE Delta_Student_InformationFlag
	ADD StudentFlagValue14 VARCHAR(20) NULL
END

GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Delta_Student_InformationFlag' AND COLUMN_NAME = 'StudentFlagValue15')
BEGIN
	ALTER TABLE Delta_Student_InformationFlag
	ADD StudentFlagValue15 VARCHAR(20) NULL
END

GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Delta_Student_InformationFlag' AND COLUMN_NAME = 'StudentFlagValue16')
BEGIN
	ALTER TABLE Delta_Student_InformationFlag
	ADD StudentFlagValue16 VARCHAR(20) NULL
END

GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Delta_Student_InformationFlag' AND COLUMN_NAME = 'StudentFlagValue17')
BEGIN
	ALTER TABLE Delta_Student_InformationFlag
	ADD StudentFlagValue17 VARCHAR(20) NULL
END

GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Delta_Student_InformationFlag' AND COLUMN_NAME = 'StudentFlagValue18')
BEGIN
	ALTER TABLE Delta_Student_InformationFlag
	ADD StudentFlagValue18 VARCHAR(20) NULL
END

GO


  IF (SELECT COUNT(ID) FROM Delta_Student_InformationFlag_Definition WHERE ID IN (13, 14, 15, 16, 17, 18)) = 0
  BEGIN
	  INSERT [Delta_Student_InformationFlag_Definition]
	  (ID, SystemName, ShortDisplayName, DisplayName, IsVisible)
	  SELECT 13, 'StudentFlagValue13', NULL, NULL, 0
	  UNION SELECT 14, 'StudentFlagValue14', NULL, NULL, 0
	  UNION SELECT 15, 'StudentFlagValue15', NULL, NULL, 0
	  UNION SELECT 16, 'StudentFlagValue16', NULL, NULL, 0
	  UNION SELECT 17, 'StudentFlagValue17', NULL, NULL, 0
	  UNION SELECT 18, 'StudentFlagValue18', NULL, NULL, 0
  END
GO



ALTER VIEW [dbo].[vDelta_Student_InformationFlag] AS 

SELECT 
Delta_Student_InformationFlag.[ID],
Delta_Student_InformationFlag.[AcademicYearID],
Delta_Student_InformationFlag.[StudentRef],
Delta_Student_InformationFlag.[OGP_StudentID],
OGP_Student.[Surname],
OGP_Student.[Forenames],
[StudentFlagValue1],
[StudentFlagValue2],
[StudentFlagValue3],
[StudentFlagValue4],
[StudentFlagValue5],
[StudentFlagValue6],
[StudentFlagValue7],
[StudentFlagValue8],
[StudentFlagValue9],
[StudentFlagValue10],
[StudentFlagValue11],
[StudentFlagValue12],
[StudentFlagValue13],
[StudentFlagValue14],
[StudentFlagValue15],
[StudentFlagValue16],
[StudentFlagValue17],
[StudentFlagValue18],
[Imported_Date]

FROM
Delta_Student_InformationFlag
INNER JOIN OGP_Student ON Delta_Student_InformationFlag.OGP_StudentID = OGP_Student.ID


GO

  
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ImportHoldingTable_DeltaStudentInformationFlag' AND COLUMN_NAME = 'StudentFlagValue13')
BEGIN
	ALTER TABLE ImportHoldingTable_DeltaStudentInformationFlag
	ADD StudentFlagValue13 VARCHAR(20) NULL
END

GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ImportHoldingTable_DeltaStudentInformationFlag' AND COLUMN_NAME = 'StudentFlagValue14')
BEGIN
	ALTER TABLE ImportHoldingTable_DeltaStudentInformationFlag
	ADD StudentFlagValue14 VARCHAR(20) NULL
END

GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ImportHoldingTable_DeltaStudentInformationFlag' AND COLUMN_NAME = 'StudentFlagValue15')
BEGIN
	ALTER TABLE ImportHoldingTable_DeltaStudentInformationFlag
	ADD StudentFlagValue15 VARCHAR(20) NULL
END

GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ImportHoldingTable_DeltaStudentInformationFlag' AND COLUMN_NAME = 'StudentFlagValue16')
BEGIN
	ALTER TABLE ImportHoldingTable_DeltaStudentInformationFlag
	ADD StudentFlagValue16 VARCHAR(20) NULL
END

GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ImportHoldingTable_DeltaStudentInformationFlag' AND COLUMN_NAME = 'StudentFlagValue17')
BEGIN
	ALTER TABLE ImportHoldingTable_DeltaStudentInformationFlag
	ADD StudentFlagValue17 VARCHAR(20) NULL
END

GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ImportHoldingTable_DeltaStudentInformationFlag' AND COLUMN_NAME = 'StudentFlagValue18')
BEGIN
	ALTER TABLE ImportHoldingTable_DeltaStudentInformationFlag
	ADD StudentFlagValue18 VARCHAR(20) NULL
END

GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--UPDATE the Delta_Category table - replace the SystemName 'EmployabilitySkills' with 'Skills'
UPDATE Delta_Category SET SystemName = 'Skills', DisplayName = 'Skills', ShortDisplayName = 'Skills' WHERE ID = 12

GO





ALTER PROCEDURE [dbo].[sp_ImportHoldingTable_DeltaStudentInformationFlag_MakeLive]

@ImportSource AS VARCHAR(3), @ImportName AS VARCHAR(20), @ImportDetails VARCHAR(500), @UserName AS VARCHAR(50)
AS

SET NOCOUNT ON

DECLARE @Error INTEGER

BEGIN TRANSACTION

--Delete all records that appear in Delta_Student_InformationFlag...

DELETE 
	Delta_Student_InformationFlag
FROM 
	Delta_Student_InformationFlag

	Set @Error = @@Error If @Error <> 0 GOTO ReportError

--Do INSERT...
INSERT Delta_Student_InformationFlag (AcademicYearID, StudentRef, OGP_StudentID, 
										StudentFlagValue1, StudentFlagValue2, StudentFlagValue3, StudentFlagValue4, StudentFlagValue5, StudentFlagValue6, 
										StudentFlagValue7, StudentFlagValue8, StudentFlagValue9, StudentFlagValue10, StudentFlagValue11, StudentFlagValue12, 
										StudentFlagValue13, StudentFlagValue14, StudentFlagValue15, StudentFlagValue16, StudentFlagValue17, StudentFlagValue18,
										Imported_Date)
SELECT 
	ImportHoldingTable_DeltaStudentInformationFlag.AcademicYearID,
	ImportHoldingTable_DeltaStudentInformationFlag.StudentRef,
	OGP_Student.ID,
	ImportHoldingTable_DeltaStudentInformationFlag.StudentFlagValue1,
	ImportHoldingTable_DeltaStudentInformationFlag.StudentFlagValue2,
	ImportHoldingTable_DeltaStudentInformationFlag.StudentFlagValue3,
	ImportHoldingTable_DeltaStudentInformationFlag.StudentFlagValue4,
	ImportHoldingTable_DeltaStudentInformationFlag.StudentFlagValue5,
	ImportHoldingTable_DeltaStudentInformationFlag.StudentFlagValue6,
	ImportHoldingTable_DeltaStudentInformationFlag.StudentFlagValue7,
	ImportHoldingTable_DeltaStudentInformationFlag.StudentFlagValue8,
	ImportHoldingTable_DeltaStudentInformationFlag.StudentFlagValue9,
	ImportHoldingTable_DeltaStudentInformationFlag.StudentFlagValue10,
	ImportHoldingTable_DeltaStudentInformationFlag.StudentFlagValue11,
	ImportHoldingTable_DeltaStudentInformationFlag.StudentFlagValue12,
	ImportHoldingTable_DeltaStudentInformationFlag.StudentFlagValue13,
	ImportHoldingTable_DeltaStudentInformationFlag.StudentFlagValue14,
	ImportHoldingTable_DeltaStudentInformationFlag.StudentFlagValue15,
	ImportHoldingTable_DeltaStudentInformationFlag.StudentFlagValue16,
	ImportHoldingTable_DeltaStudentInformationFlag.StudentFlagValue17,
	ImportHoldingTable_DeltaStudentInformationFlag.StudentFlagValue18,
	GETDATE() AS Imported_Date

FROM
	ImportHoldingTable_DeltaStudentInformationFlag
		INNER JOIN OGP_Student
		ON ImportHoldingTable_DeltaStudentInformationFlag.StudentRef  = OGP_Student.StudentRef
		AND ImportHoldingTable_DeltaStudentInformationFlag.AcademicYearID = OGP_Student.AcademicYearID				

WHERE NOT EXISTS
	(
	SELECT Delta_Student_InformationFlag.ID 
	FROM Delta_Student_InformationFlag
	)
	
	Set @Error = @@Error If @Error <> 0 GOTO ReportError

	--Record the Import History
		INSERT ImportHistory (ImportDate, ImportBy, ImportSource, ImportName, ImportDetails)
		VALUES (GetDate(), @UserName, @ImportSource, @ImportName, @ImportDetails)

	Set @Error = @@Error If @Error <> 0 GOTO ReportError

	COMMIT TRANSACTION

CleanUp:
	--Clear the records from the Import Holding table
	DELETE FROM ImportHoldingTable_DeltaStudentInformationFlag

	RETURN

ReportError:
	IF @@TranCount > 0 ROLLBACK TRANSACTION
	RAISERROR('There was a problem executing the Procedure: sp_ImportHoldingTable_DeltaStudentInformationFlag_MakeLive ', 16, 1) WITH SETERROR
	GOTO CleanUp



GO



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[sp_WebPlus_Delta_EnrolmentInfo_Select] 
  @ID             AS VARCHAR(10), 
  @GroupingType   AS VARCHAR(20), 
  @ShowAllCourses AS BIT, 
  @AcademicYearID AS VARCHAR(5), 
  @StaffID        AS INT
AS
-- =============================================
-- Author:		Andrew Genner
-- Create date: 17/07/2023
-- Modified:    03/09/2023 AndyB Added GroupingType=Student
-- Modified:	08/09/2023 AMG Added CompletionID, Add in all 20 Delta Categories
-- Modified:	09/10/2023 AMG Corrected SQL to return "All" Courses data and added RecordStatus <> 'Obsolete'.  
--						   Added 'STUDENTVIEW' to limit Categories WHERE ContributeToStudentScore = 1 and ensured that we don't show Active Delta Categories
-- Modified:	15/12/2023 AMG When populating #Temp, it now uses "Delta_Category.ID AS DeltaCategoryID".  Also simplified the Delta_Category.IsActive =1 bit to work on LEFT JOIN
-- Modified:	16/01/2023 AMG Added StudentFlagValue13-18, Changeed Delta Category EmployabilitySkills to Skills
-- Description:	This is to display the OneGrade Plus Delta Enrolment details
-- Pass in the ID and the Grouping Type (Group, Course, LearningAimRef, CollegeStructure)
-- The Enrolment Course Values produced can be restricted to those concerned with the GroupingType passed through (@ShowAllCourses = 0)
-- Alternatively, If we want top show ALL Course Enrolment values related to the GroupingType passed through, we can @ShowAllCourses = 1.
-- It is then joined with Student and StudentFlag information
------------------------------------------------
-------------------
--Remember, Group, Course and CollegeStructure will be passed @ID as an integer, so this is CAST as an integer when used
--LearningAimRef is passed @ID as a varchar
-------------------
--exec sp_WebPlus_Delta_EnrolmentInfo_Select '1469', 'Group', 1, '19/20', 2020
--exec sp_WebPlus_Delta_EnrolmentInfo_Select '1716', 'Course', 1, '19/20', 2020
--exec sp_WebPlus_Delta_EnrolmentInfo_Select '50053164', 'LearningAimRef', 1, '19/20', 2020
--exec sp_WebPlus_Delta_EnrolmentInfo_Select '3', 'CollegeStructure', 1, '19/20', 2020
--exec sp_WebPlus_Delta_EnrolmentInfo_Select '30147759', 'STUDENT', 1, '19/20', 2020

--e.g.
--ID '576' 'Group' = 'A016L001', '19/20'
--ID '1716' 'Course' = 'A016', '19/20'
--ID '50053164' 'LearningAimRef' = '50053164', '19/20'
--ID '3' 'CollegeStructure' = 'Arts School', '19/20'

--@ShowAllCourses = 1 --We want to show all Enrolment Delta Values for Students in this group / course / LearningAim / CollegeStructure
--@ShowAllCourses = 0 --We only want to show Enrolment Delta Values for Enrolments in this Group / course / LearningAim / CollegeStructure

--#Temp holds our basic information which we need to pivot.
CREATE TABLE #Temp (
	AcademicYearID varchar(5) NOT NULL,
	OGP_EnrolmentID INT NOT NULL,
	OGP_StudentID INT NOT NULL,
	CourseID INT NULL,
	CourseCode VARCHAR(50) NULL,
	CompletionID INT NULL,
	DeltaCategoryID INT NULL,
	DisplayName VARCHAR(100) NULL,
	[Value] DECIMAL(19,2) NULL,
	[Information] VARCHAR(1500) NULL,
	[Imported_Date] DATETIME NULL
					)
--We have a Group - OGP_Group.ID 
--We need to show a list of enrols ( from Delta_Enrolment_Information ) related to that group: @GroupID

--Imagine a Group on Course C
--It contains enrols from Courses A and B
--we need to show the Original Courses for the enrolments - not course C
--becasue we are importing Enrolment Info for Courses A and B for the students - not course C

--In the delta page, we need the enrolment information from the students Original Courses..
--This will display data for each *enrolment* within the group

----------------------------------------------------------
--Populate a #Temp with the information we need to display
----------------------------------------------------------
IF @ShowAllCourses = 1 AND @GroupingType = 'Group'
BEGIN

	SELECT 
		OGP_Student.ID
	INTO 
		#TempStudents
	FROM
		OGP_Student
				INNER JOIN OGP_Enrolment
				ON OGP_Student.ID = OGP_Enrolment.OGP_StudentID
				AND OGP_Enrolment.RecordStatus <> 'Obsolete'
				AND OGP_Student.RecordStatus <> 'Obsolete'
					INNER JOIN OGP_GroupEnrolment
					ON OGP_GroupEnrolment.OGP_EnrolmentID = OGP_Enrolment.ID
					AND OGP_GroupEnrolment.RecordStatus <> 'Obsolete'
	WHERE
			OGP_GroupEnrolment.OGP_GroupID = CAST(@ID AS INT)
			AND OGP_Enrolment.AcademicYearID = @AcademicYearID

	INSERT #Temp (AcademicYearID, OGP_EnrolmentID, OGP_StudentID, CourseID, CourseCode, CompletionID, DeltaCategoryID, DisplayName, [Value], [Information], Imported_Date)
	SELECT DISTINCT
		OGP_Enrolment.AcademicYearID,
		OGP_Enrolment.ID AS OGP_EnrolmentID,
		OGP_Student.ID AS OGP_StudentID,
		Course.ID,
		Course.CourseCode AS CourseCode,
		OGP_Enrolment.OverallCompletionID,
		--Delta_Enrolment_Information.DeltaCategoryID,
		DC.ID AS DeltaCategoryID,
		DC.DisplayName,
		Delta_Enrolment_Information.[Value],
		Delta_Enrolment_Information.[Information],
		Delta_Enrolment_Information.Imported_Date
	FROM
		OGP_Student
			INNER JOIN OGP_Enrolment
			ON OGP_Student.ID = OGP_Enrolment.OGP_StudentID
			AND OGP_Enrolment.RecordStatus <> 'Obsolete'
			AND OGP_Student.RecordStatus <> 'Obsolete'
				INNER JOIN #TempStudents
				ON #TempStudents.ID = OGP_Enrolment.OGP_StudentID
					INNER JOIN Course 
					ON OGP_Enrolment.OriginalCourseID = Course.ID
						LEFT JOIN Delta_Enrolment_Information
						ON Delta_Enrolment_Information.OGP_StudentID = OGP_Enrolment.OGP_StudentID
						AND Delta_Enrolment_Information.CourseID = Course.ID
								LEFT JOIN (SELECT Delta_Category.ID, Delta_Category.DisplayName FROM Delta_Category WHERE Delta_Category.IsActive = 1) DC
								ON Delta_Enrolment_Information.DeltaCategoryID = DC.ID	

		
END
--
IF @ShowAllCourses = 0 AND @GroupingType = 'Group'
BEGIN
	INSERT #Temp (AcademicYearID, OGP_EnrolmentID, OGP_StudentID, CourseID, CourseCode, CompletionID, DeltaCategoryID, DisplayName, [Value], [Information], Imported_Date)
	SELECT DISTINCT
		OGP_Enrolment.AcademicYearID,
		OGP_Enrolment.ID AS OGP_EnrolmentID,
		OGP_Student.ID AS OGP_StudentID,
		COALESCE(Delta_Enrolment_Information.CourseID, EnrolmentCourse.ID) AS CourseID,
		COALESCE(Course.CourseCode, EnrolmentCourse.CourseCode) AS CourseCode,
		OGP_Enrolment.OverallCompletionID,
		--Delta_Enrolment_Information.DeltaCategoryID,
		Delta_Category.ID AS DeltaCategoryID,
		Delta_Category.DisplayName,
		Delta_Enrolment_Information.[Value],
		Delta_Enrolment_Information.[Information],
		Delta_Enrolment_Information.Imported_Date
	FROM
		OGP_Student
			INNER JOIN OGP_Enrolment
			ON OGP_Student.ID = OGP_Enrolment.OGP_StudentID
				INNER JOIN OGP_GroupEnrolment
				ON OGP_GroupEnrolment.OGP_EnrolmentID = OGP_Enrolment.ID
					INNER JOIN Course EnrolmentCourse 
					ON OGP_Enrolment.OriginalCourseID = EnrolmentCourse.ID
						LEFT JOIN Delta_Enrolment_Information
						ON Delta_Enrolment_Information.CourseID = OGP_Enrolment.OriginalCourseID --Important
						AND Delta_Enrolment_Information.OGP_StudentID = OGP_Enrolment.OGP_StudentID
							LEFT JOIN Course
							ON Delta_Enrolment_Information.CourseID = Course.ID
								LEFT JOIN Delta_Category
								ON Delta_Enrolment_Information.DeltaCategoryID = Delta_Category.ID	
								AND Delta_Category.IsActive = 1
												
	WHERE 
		OGP_GroupEnrolment.OGP_GroupID = CAST(@ID AS INT)
		AND OGP_Enrolment.AcademicYearID = @AcademicYearID
		AND OGP_Student.RecordStatus <> 'Obsolete'
		AND OGP_Enrolment.RecordStatus <> 'Obsolete'
		AND OGP_GroupEnrolment.RecordStatus <> 'Obsolete'

END

--STUDENT
--At the student view, we show all courses, so there is no need to have "ShowAllCourses" option...
IF @GroupingType = 'STUDENT'
BEGIN
	INSERT #Temp (AcademicYearID, OGP_EnrolmentID, OGP_StudentID, CourseID, CourseCode, CompletionID, DeltaCategoryID, DisplayName, [Value], [Information], Imported_Date)
	SELECT DISTINCT
		OGP_Enrolment.AcademicYearID,
		OGP_Enrolment.ID AS OGP_EnrolmentID,
		OGP_Student.ID AS OGP_StudentID,
		COALESCE(Delta_Enrolment_Information.CourseID, EnrolmentCourse.ID) AS CourseID,
		COALESCE(Course.CourseCode, EnrolmentCourse.CourseCode) AS CourseCode,
		OGP_Enrolment.OverallCompletionID,
		--Delta_Enrolment_Information.DeltaCategoryID,
		Delta_Category.ID AS DeltaCategoryID,
		Delta_Category.DisplayName,
		Delta_Enrolment_Information.[Value],
		Delta_Enrolment_Information.[Information],
		Delta_Enrolment_Information.Imported_Date
	FROM
		OGP_Student
			INNER JOIN OGP_Enrolment
			ON OGP_Student.ID = OGP_Enrolment.OGP_StudentID
				INNER JOIN Course EnrolmentCourse 
				ON OGP_Enrolment.OriginalCourseID = EnrolmentCourse.ID
					LEFT JOIN Delta_Enrolment_Information
					ON Delta_Enrolment_Information.CourseID = OGP_Enrolment.OriginalCourseID --Important
					AND Delta_Enrolment_Information.OGP_StudentID = OGP_Enrolment.OGP_StudentID
						LEFT JOIN Course
						ON Delta_Enrolment_Information.CourseID = Course.ID
							LEFT JOIN Delta_Category
							ON Delta_Enrolment_Information.DeltaCategoryID = Delta_Category.ID
							AND Delta_Category.IsActive = 1
	WHERE 
		OGP_Student.StudentRef = @ID 
		AND OGP_Enrolment.AcademicYearID = @AcademicYearID
		AND OGP_Student.RecordStatus <> 'Obsolete'
		AND OGP_Enrolment.RecordStatus <> 'Obsolete'

END

--STUDENT
--At the student view, we show all courses, so there is no need to have "ShowAllCourses" option...
IF @GroupingType = 'STUDENTVIEW'
BEGIN
	INSERT #Temp (AcademicYearID, OGP_EnrolmentID, OGP_StudentID, CourseID, CourseCode, CompletionID, DeltaCategoryID, DisplayName, [Value], [Information], Imported_Date)
	SELECT DISTINCT
		OGP_Enrolment.AcademicYearID,
		OGP_Enrolment.ID AS OGP_EnrolmentID,
		OGP_Student.ID AS OGP_StudentID,
		COALESCE(Delta_Enrolment_Information.CourseID, EnrolmentCourse.ID) AS CourseID,
		COALESCE(Course.CourseCode, EnrolmentCourse.CourseCode) AS CourseCode,
		OGP_Enrolment.OverallCompletionID,
		--Delta_Enrolment_Information.DeltaCategoryID,
		Delta_Category.ID AS DeltaCategoryID,
		Delta_Category.DisplayName,
		Delta_Enrolment_Information.[Value],
		Delta_Enrolment_Information.[Information],
		Delta_Enrolment_Information.Imported_Date
	FROM
		OGP_Student
			INNER JOIN OGP_Enrolment
			ON OGP_Student.ID = OGP_Enrolment.OGP_StudentID
				INNER JOIN Course EnrolmentCourse 
					ON OGP_Enrolment.OriginalCourseID = EnrolmentCourse.ID
					LEFT JOIN Delta_Enrolment_Information
					ON Delta_Enrolment_Information.CourseID = OGP_Enrolment.OriginalCourseID --Important
					AND Delta_Enrolment_Information.OGP_StudentID = OGP_Enrolment.OGP_StudentID
						LEFT JOIN Course
						ON Delta_Enrolment_Information.CourseID = Course.ID
							LEFT JOIN Delta_Category
							ON Delta_Enrolment_Information.DeltaCategoryID = Delta_Category.ID
							AND Delta_Category.IsActive = 1
							AND Delta_Category.ContributeToStudentScore = 1
	WHERE 
		OGP_Student.StudentRef = @ID 
		AND OGP_Enrolment.AcademicYearID = @AcademicYearID
		AND OGP_Student.RecordStatus <> 'Obsolete'
		AND OGP_Enrolment.RecordStatus <> 'Obsolete'

END


--COURSE
IF @ShowAllCourses = 1 AND @GroupingType = 'Course'
BEGIN
	INSERT #Temp (AcademicYearID, OGP_EnrolmentID, OGP_StudentID, CourseID, CourseCode, CompletionID, DeltaCategoryID, DisplayName, [Value], [Information], Imported_Date)
	SELECT DISTINCT
		OGP_Enrolment.AcademicYearID,
		OGP_Enrolment.ID AS OGP_EnrolmentID,
		OGP_Student.ID AS OGP_StudentID,
		COALESCE(Delta_Enrolment_Information.CourseID, EnrolmentCourse.ID) AS CourseID,
		COALESCE(Course.CourseCode, EnrolmentCourse.CourseCode) AS CourseCode,
		OGP_Enrolment.OverallCompletionID,
		--Delta_Enrolment_Information.DeltaCategoryID,
		Delta_Category.ID AS DeltaCategoryID,
		Delta_Category.DisplayName,
		Delta_Enrolment_Information.[Value],
		Delta_Enrolment_Information.[Information],
		Delta_Enrolment_Information.Imported_Date
	FROM
		OGP_Student
			INNER JOIN OGP_Enrolment
			ON OGP_Student.ID = OGP_Enrolment.OGP_StudentID
				INNER JOIN Course EnrolmentCourse 
				ON OGP_Enrolment.OriginalCourseID = EnrolmentCourse.ID
					LEFT JOIN Delta_Enrolment_Information
					ON Delta_Enrolment_Information.OGP_StudentID = OGP_Enrolment.OGP_StudentID
						LEFT JOIN Course
						ON Delta_Enrolment_Information.CourseID = Course.ID
							LEFT JOIN Delta_Category
							ON Delta_Enrolment_Information.DeltaCategoryID = Delta_Category.ID
							AND Delta_Category.IsActive = 1
							
	WHERE 
		OGP_Enrolment.CourseID = CAST(@ID AS INT)
		AND OGP_Enrolment.AcademicYearID = @AcademicYearID
		AND OGP_Student.RecordStatus <> 'Obsolete'
		AND OGP_Enrolment.RecordStatus <> 'Obsolete'
END
--
IF @ShowAllCourses = 0 AND @GroupingType = 'Course'
BEGIN
	INSERT #Temp (AcademicYearID, OGP_EnrolmentID, OGP_StudentID, CourseID, CourseCode, CompletionID, DeltaCategoryID, DisplayName, [Value], [Information], Imported_Date)
	SELECT DISTINCT
		OGP_Enrolment.AcademicYearID,
		OGP_Enrolment.ID AS OGP_EnrolmentID,
		OGP_Student.ID AS OGP_StudentID,
		COALESCE(Delta_Enrolment_Information.CourseID, EnrolmentCourse.ID) AS CourseID,
		COALESCE(Course.CourseCode, EnrolmentCourse.CourseCode) AS CourseCode,
		OGP_Enrolment.OverallCompletionID,
		--Delta_Enrolment_Information.DeltaCategoryID,
		Delta_Category.ID AS DeltaCategoryID,
		Delta_Category.DisplayName,
		Delta_Enrolment_Information.[Value],
		Delta_Enrolment_Information.[Information],
		Delta_Enrolment_Information.Imported_Date
	FROM
		OGP_Student
			INNER JOIN OGP_Enrolment
			ON OGP_Student.ID = OGP_Enrolment.OGP_StudentID
				INNER JOIN Course EnrolmentCourse 
				ON OGP_Enrolment.OriginalCourseID = EnrolmentCourse.ID
					LEFT JOIN Delta_Enrolment_Information
					ON Delta_Enrolment_Information.CourseID = OGP_Enrolment.OriginalCourseID --Important
					AND Delta_Enrolment_Information.OGP_StudentID = OGP_Enrolment.OGP_StudentID
						LEFT JOIN Course
						ON Delta_Enrolment_Information.CourseID = Course.ID
							LEFT JOIN Delta_Category
							ON Delta_Enrolment_Information.DeltaCategoryID = Delta_Category.ID
							AND Delta_Category.IsActive = 1

	WHERE 
		OGP_Enrolment.CourseID = CAST(@ID AS INT)
		AND OGP_Enrolment.AcademicYearID = @AcademicYearID
		AND OGP_Student.RecordStatus <> 'Obsolete'
		AND OGP_Enrolment.RecordStatus <> 'Obsolete'

END

--LearningAim
IF @ShowAllCourses = 1 AND @GroupingType = 'LearningAim'
BEGIN
	INSERT #Temp (AcademicYearID, OGP_EnrolmentID, OGP_StudentID, CourseID, CourseCode, CompletionID, DeltaCategoryID, DisplayName, [Value], [Information], Imported_Date)
	SELECT DISTINCT
		OGP_Enrolment.AcademicYearID,
		OGP_Enrolment.ID AS OGP_EnrolmentID,
		OGP_Student.ID AS OGP_StudentID,
		COALESCE(Delta_Enrolment_Information.CourseID, EnrolmentCourse.ID) AS CourseID,
		COALESCE(Course.CourseCode, EnrolmentCourse.CourseCode) AS CourseCode,
		OGP_Enrolment.OverallCompletionID,
		--Delta_Enrolment_Information.DeltaCategoryID,
		Delta_Category.ID AS DeltaCategoryID,
		Delta_Category.DisplayName,
		Delta_Enrolment_Information.[Value],
		Delta_Enrolment_Information.[Information],
		Delta_Enrolment_Information.Imported_Date
	FROM
		OGP_Student
			INNER JOIN OGP_Enrolment
			ON OGP_Student.ID = OGP_Enrolment.OGP_StudentID
				INNER JOIN OGP_EnrolmentStatus
				ON OGP_Enrolment.ID = OGP_EnrolmentStatus.OGP_EnrolmentID
					INNER JOIN Course EnrolmentCourse 
					ON OGP_Enrolment.OriginalCourseID = EnrolmentCourse.ID
						LEFT JOIN Delta_Enrolment_Information
						ON Delta_Enrolment_Information.OGP_StudentID = OGP_Enrolment.OGP_StudentID
							LEFT JOIN Course
							ON Delta_Enrolment_Information.CourseID = Course.ID
								LEFT JOIN Delta_Category
								ON Delta_Enrolment_Information.DeltaCategoryID = Delta_Category.ID
								AND Delta_Category.IsActive = 1
	WHERE 
		OGP_EnrolmentStatus.LearningAimRef = @ID
		AND OGP_Enrolment.AcademicYearID = @AcademicYearID
		AND OGP_Student.RecordStatus <> 'Obsolete'
		AND OGP_Enrolment.RecordStatus <> 'Obsolete'
		AND OGP_EnrolmentStatus.RecordStatus <> 'Obsolete'

END
IF @ShowAllCourses = 0 AND @GroupingType = 'LearningAim'
BEGIN
	INSERT #Temp (AcademicYearID, OGP_EnrolmentID, OGP_StudentID, CourseID, CourseCode, CompletionID, DeltaCategoryID, DisplayName, [Value], [Information], Imported_Date)
	SELECT DISTINCT
		OGP_Enrolment.AcademicYearID,
		OGP_Enrolment.ID AS OGP_EnrolmentID,
		OGP_Student.ID AS OGP_StudentID,
		COALESCE(Delta_Enrolment_Information.CourseID, EnrolmentCourse.ID) AS CourseID,
		COALESCE(Course.CourseCode, EnrolmentCourse.CourseCode) AS CourseCode,
		OGP_Enrolment.OverallCompletionID,
		--Delta_Enrolment_Information.DeltaCategoryID,
		Delta_Category.ID AS DeltaCategoryID,
		Delta_Category.DisplayName,
		Delta_Enrolment_Information.[Value],
		Delta_Enrolment_Information.[Information],
		Delta_Enrolment_Information.Imported_Date
	FROM
		OGP_Student
			INNER JOIN OGP_Enrolment
			ON OGP_Student.ID = OGP_Enrolment.OGP_StudentID
				INNER JOIN OGP_EnrolmentStatus
				ON OGP_Enrolment.ID = OGP_EnrolmentStatus.OGP_EnrolmentID
					INNER JOIN Course EnrolmentCourse 
					ON OGP_Enrolment.OriginalCourseID = EnrolmentCourse.ID
						LEFT JOIN Delta_Enrolment_Information
						ON Delta_Enrolment_Information.CourseID = OGP_Enrolment.OriginalCourseID --Important
						AND Delta_Enrolment_Information.OGP_StudentID = OGP_Enrolment.OGP_StudentID
							LEFT JOIN Course
							ON Delta_Enrolment_Information.CourseID = Course.ID
								LEFT JOIN Delta_Category
								ON Delta_Enrolment_Information.DeltaCategoryID = Delta_Category.ID
								AND Delta_Category.IsActive = 1
	WHERE 
		OGP_EnrolmentStatus.LearningAimRef = @ID
		AND OGP_Enrolment.AcademicYearID = @AcademicYearID
		AND OGP_Student.RecordStatus <> 'Obsolete'
		AND OGP_Enrolment.RecordStatus <> 'Obsolete'
		AND OGP_EnrolmentStatus.RecordStatus <> 'Obsolete'

END

--CollegeStructure (DEPT)
IF @ShowAllCourses = 1 AND (@GroupingType = 'CollegeStructure' OR @GroupingType = 'DEPARTMENT' OR @GroupingType = 'DEPT')
BEGIN
	INSERT #Temp (AcademicYearID, OGP_EnrolmentID, OGP_StudentID, CourseID, CourseCode, CompletionID, DeltaCategoryID, DisplayName, [Value], [Information], Imported_Date)
	SELECT DISTINCT
		OGP_Enrolment.AcademicYearID,
		OGP_Enrolment.ID AS OGP_EnrolmentID,
		OGP_Student.ID AS OGP_StudentID,
		COALESCE(Delta_Enrolment_Information.CourseID, Course.ID) AS CourseID,
		Course.CourseCode AS CourseCode,
		OGP_Enrolment.OverallCompletionID,
		--Delta_Enrolment_Information.DeltaCategoryID,
		Delta_Category.ID AS DeltaCategoryID,
		Delta_Category.DisplayName,
		Delta_Enrolment_Information.[Value],
		Delta_Enrolment_Information.[Information],
		Delta_Enrolment_Information.Imported_Date
	FROM
		OGP_Student
			INNER JOIN OGP_Enrolment
			ON OGP_Student.ID = OGP_Enrolment.OGP_StudentID
				INNER JOIN OGP_GroupEnrolment
				ON OGP_GroupEnrolment.OGP_EnrolmentID = OGP_Enrolment.ID
					INNER JOIN OGP_Group
					ON OGP_GroupEnrolment.OGP_GroupID = OGP_Group.ID
						INNER JOIN  OGP_CollegeStructureGroup
						ON OGP_Group.ID = OGP_CollegeStructureGroup.OGP_GroupID
							INNER JOIN Delta_Enrolment_Information
							ON Delta_Enrolment_Information.OGP_StudentID = OGP_Student.ID
								INNER JOIN Course  
								ON Delta_Enrolment_Information.CourseID = Course.ID
									LEFT JOIN Delta_Category
									ON Delta_Enrolment_Information.DeltaCategoryID = Delta_Category.ID
									AND Delta_Category.IsActive = 1
	WHERE 
		OGP_CollegeStructureGroup.OGP_CollegeStructureID = CAST(@ID AS INT)
		AND OGP_Enrolment.AcademicYearID = @AcademicYearID
		AND OGP_CollegeStructureGroup.RecordStatus <> 'Obsolete'
		AND OGP_Group.RecordStatus <> 'Obsolete'
		AND OGP_GroupEnrolment.RecordStatus <> 'Obsolete'
		AND OGP_Student.RecordStatus <> 'Obsolete'
		AND OGP_Enrolment.RecordStatus <> 'Obsolete'
		
END
IF @ShowAllCourses = 0 AND (@GroupingType = 'CollegeStructure' OR @GroupingType = 'DEPARTMENT' OR @GroupingType = 'DEPT')
BEGIN
	INSERT #Temp (AcademicYearID, OGP_EnrolmentID, OGP_StudentID, CourseID, CourseCode, CompletionID, DeltaCategoryID, DisplayName, [Value], [Information], Imported_Date)
	SELECT DISTINCT
		OGP_Enrolment.AcademicYearID,
		OGP_Enrolment.ID AS OGP_EnrolmentID,
		OGP_Student.ID AS OGP_StudentID,
		COALESCE(Delta_Enrolment_Information.CourseID, Course.ID) AS CourseID,
		Course.CourseCode AS CourseCode,
		OGP_Enrolment.OverallCompletionID,
		--Delta_Enrolment_Information.DeltaCategoryID,
		Delta_Category.ID AS DeltaCategoryID,
		Delta_Category.DisplayName,
		Delta_Enrolment_Information.[Value],
		Delta_Enrolment_Information.[Information],
		Delta_Enrolment_Information.Imported_Date
	FROM
		OGP_Student
			INNER JOIN OGP_Enrolment
			ON OGP_Student.ID = OGP_Enrolment.OGP_StudentID
				INNER JOIN OGP_GroupEnrolment
				ON OGP_GroupEnrolment.OGP_EnrolmentID = OGP_Enrolment.ID
					INNER JOIN OGP_Group
					ON OGP_GroupEnrolment.OGP_GroupID = OGP_Group.ID
						INNER JOIN  OGP_CollegeStructureGroup
						ON OGP_Group.ID = OGP_CollegeStructureGroup.OGP_GroupID
							INNER JOIN Course  
							ON OGP_Enrolment.OriginalCourseID = Course.ID
								INNER JOIN Delta_Enrolment_Information
								ON Delta_Enrolment_Information.CourseID = Course.ID
								AND Delta_Enrolment_Information.OGP_StudentID = OGP_Student.ID
										LEFT JOIN Delta_Category
										ON Delta_Enrolment_Information.DeltaCategoryID = Delta_Category.ID
										AND Delta_Category.IsActive = 1
	WHERE 
		OGP_CollegeStructureGroup.OGP_CollegeStructureID = CAST(@ID AS INT)
		AND OGP_Enrolment.AcademicYearID = @AcademicYearID
		AND OGP_CollegeStructureGroup.RecordStatus <> 'Obsolete'
		AND OGP_Group.RecordStatus <> 'Obsolete'
		AND OGP_GroupEnrolment.RecordStatus <> 'Obsolete'
		AND OGP_Student.RecordStatus <> 'Obsolete'
		AND OGP_Enrolment.RecordStatus <> 'Obsolete'

END


----------------------------------------------------------
--Populate a #TempPivot with the VALUES we need to display
----------------------------------------------------------

SELECT 
	*
INTO #TempPivot
FROM  
	(
	  SELECT 
		  Delta_Category.SystemName AS DeltaSystemName, 
		  #Temp.AcademicYearID,
		  #Temp.[OGP_StudentID], 
		  #Temp.OGP_EnrolmentID,
		  #Temp.[CourseID], 
		  #Temp.CourseCode,
		  #Temp.CompletionID,
		  [Value]   
	  FROM 
		#Temp
		  LEFT JOIN Delta_Category
		  ON Delta_Category.ID = #Temp.DeltaCategoryID
	) AS SourceTable  
	PIVOT  
	(  
	  MAX([Value])--There should only be one value, but we need MAX, MIN, SUM, COUNT or something.
	  FOR DeltaSystemName IN ([Attendance], [Punctuality], [Progress], [WorkOutstanding], 
								[LateStart], [ExceptionalEntry], [PTWorkHours], [WorkPlacement], 
								[Enrichment], [Comments], [Behavioural], [Skills],
								[User1], [User2], [User3], [User4],
								[User5], [User6], [User7], [User8])
	) AS PivotTable
  
-------------------------------------------------------------------
--Populate a #TempPivotInfo with the Information we need to display
-------------------------------------------------------------------
SELECT 
	OGP_EnrolmentID,
	[Attendance] AS [Attendance_Info], 
	[Punctuality] AS [Punctuality_Info], 
	[Progress] AS [Progress_Info] ,
	[WorkOutstanding] AS [WorkOutstanding_Info],
	[LateStart] AS [LateStart_Info],
	[ExceptionalEntry] AS [ExceptionalEntry_Info],
	[PTWorkHours] AS [PTWorkHours_Info],
	[WorkPlacement] AS [WorkPlacement_Info],
	[Enrichment] AS [Enrichment_Info], 
	[Comments] AS [Comments_Info],
	[Behavioural] AS [Behavioural_Info],
	[Skills] AS [Skills_Info],
	[User1] AS [User1_Info],
	[User2] AS [User2_Info],
	[User3] AS [User3_Info],
	[User4] AS [User4_Info],
	[User5] AS [User5_Info],
	[User6] AS [User6_Info],
	[User7] AS [User7_Info],
	[User8] AS [User8_Info]
INTO #TempPivotInfo
FROM  
	(
	  SELECT 
		  Delta_Category.SystemName AS DeltaSystemName, 
		  #Temp.OGP_EnrolmentID,
		  #Temp.Information
	  FROM 
		#Temp
		  LEFT JOIN Delta_Category
		  ON Delta_Category.ID = #Temp.DeltaCategoryID
	) AS SourceTable  
	PIVOT  
	(  
	  MAX([Information])--There should only be one value, but we need MAX, MIN, SUM, COUNT or something.
	  FOR DeltaSystemName IN ([Attendance], [Punctuality], [Progress], [WorkOutstanding], 
								[LateStart], [ExceptionalEntry], [PTWorkHours], [WorkPlacement], 
								[Enrichment], [Comments], [Behavioural], [Skills],
								[User1], [User2], [User3], [User4],
								[User5], [User6], [User7], [User8]) 
	) AS PivotTable


-------------------------------------------------------------
--SELECT out the results joining #TempPivot to #TempPivotInfo
-------------------------------------------------------------
SELECT DISTINCT
	#TempPivot.*, 
	OGP_Student.StudentRef, 
	OGP_Student.Forenames, 
	OGP_Student.Surname, 
	OGP_Enrolment.OverallCompletionID, (SELECT [Description] FROM Completion WHERE ID = OGP_Enrolment.OverallCompletionID) AS CompletionStatus,
	(SELECT Course.Title FROM Course WHERE Course.ID = #TempPivot.CourseID) AS CourseTitle,
	#TempPivotInfo.[Attendance_Info], #TempPivotInfo.[Punctuality_Info], #TempPivotInfo.[Progress_Info], #TempPivotInfo.[WorkOutstanding_Info],
	#TempPivotInfo.[LateStart_Info], #TempPivotInfo.[ExceptionalEntry_Info], #TempPivotInfo.[PTWorkHours_Info], #TempPivotInfo.[WorkPlacement_Info],
	#TempPivotInfo.[Enrichment_Info], #TempPivotInfo.[Comments_Info], #TempPivotInfo.[Behavioural_Info], #TempPivotInfo.[Skills_Info],
	#TempPivotInfo.[User1_Info], #TempPivotInfo.[User2_Info], #TempPivotInfo.[User3_Info], #TempPivotInfo.[User4_Info], 
	#TempPivotInfo.[User5_Info], #TempPivotInfo.[User6_Info], #TempPivotInfo.[User7_Info], #TempPivotInfo.[User8_Info],
	Delta_Student_InformationFlag.StudentFlagValue1, StudentFlagValue2, StudentFlagValue3, StudentFlagValue4,
	StudentFlagValue5, StudentFlagValue6, StudentFlagValue7, StudentFlagValue8,
	StudentFlagValue9, StudentFlagValue10, StudentFlagValue11, StudentFlagValue12,
	StudentFlagValue13, StudentFlagValue14,	StudentFlagValue15, StudentFlagValue16, StudentFlagValue17, StudentFlagValue18,
	CASE WHEN Delta_Watchlist.OGP_StudentID = OGP_Student.ID THEN 1 ELSE 0 END AS IsInWatchlist
FROM 
	#TempPivotInfo
	INNER JOIN #TempPivot
	ON #TempPivotInfo.OGP_EnrolmentID = #TempPivot.OGP_EnrolmentID
		INNER JOIN OGP_Student
		ON #TempPivot.OGP_StudentID = OGP_Student.ID
			INNER JOIN OGP_Enrolment
			ON #TempPivotInfo.OGP_EnrolmentID = OGP_Enrolment.ID 
				LEFT JOIN Delta_Student_InformationFlag
				ON OGP_Student.ID  = Delta_Student_InformationFlag.OGP_StudentID
					LEFT JOIN Delta_Watchlist
					ON OGP_Student.ID = Delta_Watchlist.OGP_StudentID
					AND Delta_Watchlist.StaffID = @StaffID
ORDER BY Surname, Forenames, StudentRef

GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[sp_WebPlus_Delta_StudentScores_Report_Select] 
  @AcademicYearID   AS VARCHAR(5), 
  @NumWeeksTrend    AS INT, 
  @StaffID          AS INT = 0, 
  @ApplyWatchlist   AS BIT = 0, 
  @ApplyStaffGroups AS BIT = 0
AS

-- =============================================
-- Author:		Andrew Genner
-- Create date: 17/07/2023
-- Modfied:		08/09/2023 AMG Added the full set of Delta Categories, Added CompletionID
-- Modified:	16/10/2023 AMG The Trend was being calculated the wrong way around - corrected. 
-- Modified:	20/10/2023 AMG - Changed Delta Category PTWorkHours to StudentSurvey
-- Modified:	04/11/2023 AndyB - Added option (@ApplyStaffGroups) to include Students for Staff Groups
-- Modified:	06/12/2023 AMG - Added in <> 'Obsolete'
-- Modified:	16/01/2023 AMG Added StudentFlagValue13-18, Changeed Delta Category EmployabilitySkills to Skills
-- Description:	
-- This is to display the OneGrade Plus Delta Scores for the overall report (menu item)
-- An Overall Student Score is calculated - as is a score for each delta category (and value)
-- It is then joined with Student and StudentFlag information
-- The trend is calculted depending on how many weeks back we want to look - passed as the @NumWeekTrend parameter.
-- If the StaffID is passed in (it is an optional parameter), filter for the Watchlist of Students for that Staff member
--------------------------------------------------------------------------------------------------------------------------
-- Example: All Students
--   EXEC sp_WebPlus_Delta_StudentScores_Report_Select '22/23', 4
--   EXEC sp_WebPlus_Delta_StudentScores_Report_Select '22/23', 4, 0, 0, 0
----------------------------------------------------------------------------------------------------------------------------
-- Example: Students for MyWatchList (StaffID=2)
--   EXEC sp_WebPlus_Delta_StudentScores_Report_Select '22/23', 4, 2, 1
--   EXEC sp_WebPlus_Delta_StudentScores_Report_Select '22/23', 4, 2, 1, 0
----------------------------------------------------------------------------------------------------------------------------
-- Example: Students for MyGroups (StaffID=2)
--   EXEC sp_WebPlus_Delta_StudentScores_Report_Select '22/23', 4, 2, 0, 1
----------------------------------------------------------------------------------------------------------------------------
-- Example: Students for MyWatchList & MyGroups (StaffID=2)
--   EXEC sp_WebPlus_Delta_StudentScores_Report_Select '22/23', 4, 2, 1, 1
----------------------------------------------------------------------------------------------------------------------------


CREATE TABLE #Temp_DeltaScore_Overall (
	AcademicYearID varchar(5) NOT NULL,
	OGP_StudentID INT NOT NULL,
	DeltaScore_Overall INT NULL,
	DeltaScore_Overall_Trend INT NULL,
	Latest_Data_Date DATETIME NULL
					)

CREATE TABLE #Temp_DeltaScore_ByCategory (
	AcademicYearID varchar(5) NOT NULL,
	OGP_StudentID INT NOT NULL,
	DeltaCategoryID INT NULL,
	DisplayName VARCHAR(100) NULL,
	DeltaScore INT NULL,
	DeltaValue DECIMAL(19,2) NULL,
	DeltaScore_Trend INT NULL
					)

CREATE TABLE #TempStudentCompletion (
	AcademicYearID varchar(5) NOT NULL,
	OGP_StudentID INT NOT NULL,
	StudentCompletionID INT NOT NULL
					)

--DECLARE @AcademicYearID varchar(5)
--SELECT @AcademicYearID = '19/20'

----------------------------------------------------------
--Populate a #Temp with the information we need to display
----------------------------------------------------------
IF @ApplyWatchlist = 0
BEGIN

	--Work out (based on the Enrolments included in the selection), the Completion ID for each Student
	INSERT #TempStudentCompletion (AcademicYearID, OGP_StudentID, StudentCompletionID)
	SELECT 
		t.AcademicYearID,
		t.OGP_StudentID,
		(CASE WHEN CountCompletionID_1 > 0 THEN 1 ELSE --cont
			(CASE WHEN CountCompletionID_2 > 0 THEN 2 ELSE --complete
				(CASE WHEN CountCompletionID_3 > 0 THEN 3 ELSE --w/drawn
					(CASE WHEN CountCompletionID_6 > 0 THEN 6 ELSE --temp w/drawn
						(CASE WHEN CountCompletionID_4 > 0 THEN 4 ELSE --Xfer
						1
						END) -- Xfer
					END)
				END)
			END)
		END) AS StudentCompletionID 
	FROM 
	(
		SELECT
			OGP_Student.AcademicYearID,
			OGP_Student.ID AS OGP_StudentID, 
			(SELECT Count(E.OverallCompletionID) FROM OGP_Enrolment E WHERE E.OverallCompletionID = 1 AND E.OGP_StudentID = OGP_Student.ID) AS CountCompletionID_1,
			(SELECT Count(E.OverallCompletionID) FROM OGP_Enrolment E WHERE E.OverallCompletionID = 2 AND E.OGP_StudentID = OGP_Student.ID) AS CountCompletionID_2,
			(SELECT Count(E.OverallCompletionID) FROM OGP_Enrolment E WHERE E.OverallCompletionID = 3 AND E.OGP_StudentID = OGP_Student.ID) AS CountCompletionID_3,
			(SELECT Count(E.OverallCompletionID) FROM OGP_Enrolment E WHERE E.OverallCompletionID = 4 AND E.OGP_StudentID = OGP_Student.ID) AS CountCompletionID_4,
			(SELECT Count(E.OverallCompletionID) FROM OGP_Enrolment E WHERE E.OverallCompletionID = 6 AND E.OGP_StudentID = OGP_Student.ID) AS CountCompletionID_6
		FROM
			OGP_Student
		WHERE 
			OGP_Student.AcademicYearID = @AcademicYearID
			AND OGP_Student.RecordStatus <> 'Obsolete'
			
	) t

	INSERT #Temp_DeltaScore_Overall (AcademicYearID, OGP_StudentID, DeltaScore_Overall, DeltaScore_Overall_Trend, Latest_Data_Date)
	SELECT 
		AcademicYearID,
		OGP_StudentID,
		Latest_Data_Score,
		Latest_Data_Score - TrendStartScore AS DeltaScore_Overall_Trend,
		Latest_Data_Date
		FROM
		(
		SELECT DISTINCT
			OGP_Enrolment.AcademicYearID,
			OGP_Student.ID AS OGP_StudentID,
			Delta_Student_Score.Latest_Data_Score,
			--Delta_Student_Score.Latest_Data_Week - @NumWeeksTrend AS TrendStartWeekNum,
			CASE (Delta_Student_Score.Latest_Data_Week - @NumWeeksTrend)
				WHEN 1 THEN Wk1_Score_Overall
				WHEN 2 THEN Wk2_Score_Overall
				WHEN 3 THEN Wk3_Score_Overall
				WHEN 4 THEN Wk4_Score_Overall
				WHEN 5 THEN Wk5_Score_Overall
				WHEN 6 THEN Wk6_Score_Overall
				WHEN 7 THEN Wk7_Score_Overall
				WHEN 8 THEN Wk8_Score_Overall
				WHEN 9 THEN Wk9_Score_Overall
				WHEN 10 THEN Wk10_Score_Overall
				WHEN 11 THEN Wk11_Score_Overall
				WHEN 12 THEN Wk12_Score_Overall
				WHEN 13 THEN Wk13_Score_Overall
				WHEN 14 THEN Wk14_Score_Overall
				WHEN 15 THEN Wk15_Score_Overall
				WHEN 16 THEN Wk16_Score_Overall
				WHEN 17 THEN Wk17_Score_Overall
				WHEN 18 THEN Wk18_Score_Overall
				WHEN 19 THEN Wk19_Score_Overall
				WHEN 20 THEN Wk20_Score_Overall
				WHEN 21 THEN Wk21_Score_Overall
				WHEN 22 THEN Wk22_Score_Overall
				WHEN 23 THEN Wk23_Score_Overall
				WHEN 24 THEN Wk24_Score_Overall
				WHEN 25 THEN Wk25_Score_Overall
				WHEN 26 THEN Wk26_Score_Overall
				WHEN 27 THEN Wk27_Score_Overall
				WHEN 28 THEN Wk28_Score_Overall
				WHEN 29 THEN Wk29_Score_Overall
				WHEN 30 THEN Wk30_Score_Overall
				WHEN 31 THEN Wk31_Score_Overall
				WHEN 32 THEN Wk32_Score_Overall
				WHEN 33 THEN Wk33_Score_Overall
				WHEN 34 THEN Wk34_Score_Overall
				WHEN 35 THEN Wk35_Score_Overall
				WHEN 36 THEN Wk36_Score_Overall
				WHEN 37 THEN Wk37_Score_Overall
				WHEN 38 THEN Wk38_Score_Overall
				WHEN 39 THEN Wk39_Score_Overall
				WHEN 40 THEN Wk40_Score_Overall
				WHEN 41 THEN Wk41_Score_Overall
				WHEN 42 THEN Wk42_Score_Overall
				WHEN 43 THEN Wk43_Score_Overall
				WHEN 44 THEN Wk44_Score_Overall
				WHEN 45 THEN Wk45_Score_Overall
				WHEN 46 THEN Wk46_Score_Overall
				WHEN 47 THEN Wk47_Score_Overall
				WHEN 48 THEN Wk48_Score_Overall
				WHEN 49 THEN Wk49_Score_Overall
				WHEN 50 THEN Wk50_Score_Overall
				WHEN 51 THEN Wk51_Score_Overall
				WHEN 52 THEN Wk52_Score_Overall
				WHEN 53 THEN Wk53_Score_Overall
			ELSE Wk1_Score_Overall END AS TrendStartScore, --If the difference ever drops below 1, just use wk1
			Delta_Student_Score.Latest_Data_Date
		FROM
			OGP_Student
				INNER JOIN OGP_Enrolment
				ON OGP_Student.ID = OGP_Enrolment.OGP_StudentID
						LEFT JOIN Delta_Student_Score
						ON Delta_Student_Score.OGP_StudentID = OGP_Student.ID
		WHERE 
			OGP_Enrolment.AcademicYearID = @AcademicYearID
			AND OGP_Student.RecordStatus <> 'Obsolete' AND OGP_Enrolment.RecordStatus <> 'Obsolete'
		) t

	INSERT #Temp_DeltaScore_ByCategory (AcademicYearID, OGP_StudentID, DeltaCategoryID, DisplayName, DeltaScore, DeltaValue, DeltaScore_Trend)
	
	SELECT 
	AcademicYearID,
	OGP_StudentID,
	DeltaCategoryID,
	DisplayName,
	Latest_Data_Score,
	Latest_Data_Value,
	Latest_Data_Score - TrendStartScore AS DeltaScore_Trend
	FROM
		(
		SELECT DISTINCT
			OGP_Enrolment.AcademicYearID,
			OGP_Student.ID AS OGP_StudentID,
			Delta_Student_Stat.DeltaCategoryID,
			Delta_Category.DisplayName,
			Delta_Student_Stat.Latest_Data_Score,
			Delta_Student_Stat.Latest_Data_Value,
			CASE (Delta_Student_Stat.Latest_Data_Week - @NumWeeksTrend)
				WHEN 1 THEN Wk1_Score
				WHEN 2 THEN Wk2_Score
				WHEN 3 THEN Wk3_Score
				WHEN 4 THEN Wk4_Score
				WHEN 5 THEN Wk5_Score
				WHEN 6 THEN Wk6_Score
				WHEN 7 THEN Wk7_Score
				WHEN 8 THEN Wk8_Score
				WHEN 9 THEN Wk9_Score
				WHEN 10 THEN Wk10_Score
				WHEN 11 THEN Wk11_Score
				WHEN 12 THEN Wk12_Score
				WHEN 13 THEN Wk13_Score
				WHEN 14 THEN Wk14_Score
				WHEN 15 THEN Wk15_Score
				WHEN 16 THEN Wk16_Score
				WHEN 17 THEN Wk17_Score
				WHEN 18 THEN Wk18_Score
				WHEN 19 THEN Wk19_Score
				WHEN 20 THEN Wk20_Score
				WHEN 21 THEN Wk21_Score
				WHEN 22 THEN Wk22_Score
				WHEN 23 THEN Wk23_Score
				WHEN 24 THEN Wk24_Score
				WHEN 25 THEN Wk25_Score
				WHEN 26 THEN Wk26_Score
				WHEN 27 THEN Wk27_Score
				WHEN 28 THEN Wk28_Score
				WHEN 29 THEN Wk29_Score
				WHEN 30 THEN Wk30_Score
				WHEN 31 THEN Wk31_Score
				WHEN 32 THEN Wk32_Score
				WHEN 33 THEN Wk33_Score
				WHEN 34 THEN Wk34_Score
				WHEN 35 THEN Wk35_Score
				WHEN 36 THEN Wk36_Score
				WHEN 37 THEN Wk37_Score
				WHEN 38 THEN Wk38_Score
				WHEN 39 THEN Wk39_Score
				WHEN 40 THEN Wk40_Score
				WHEN 41 THEN Wk41_Score
				WHEN 42 THEN Wk42_Score
				WHEN 43 THEN Wk43_Score
				WHEN 44 THEN Wk44_Score
				WHEN 45 THEN Wk45_Score
				WHEN 46 THEN Wk46_Score
				WHEN 47 THEN Wk47_Score
				WHEN 48 THEN Wk48_Score
				WHEN 49 THEN Wk49_Score
				WHEN 50 THEN Wk50_Score
				WHEN 51 THEN Wk51_Score
				WHEN 52 THEN Wk52_Score
				WHEN 53 THEN Wk53_Score
			ELSE Wk1_Score END AS TrendStartScore --If the difference ever drops below 1, just use wk1

		FROM
			OGP_Student
				INNER JOIN OGP_Enrolment
				ON OGP_Student.ID = OGP_Enrolment.OGP_StudentID
						LEFT JOIN Delta_Student_Stat
						ON Delta_Student_Stat.OGP_StudentID = OGP_Student.ID
							LEFT JOIN Delta_Category
							ON Delta_Student_Stat.DeltaCategoryID = Delta_Category.ID
							AND Delta_Category.IsActive = 1
		WHERE 
			OGP_Enrolment.AcademicYearID = @AcademicYearID
			AND OGP_Student.RecordStatus <> 'Obsolete' AND OGP_Enrolment.RecordStatus <> 'Obsolete'
		) t

END

IF @ApplyWatchlist = 1
BEGIN

	--Work out (based on the Enrolments included in the selection), the Completion ID for each Student
	INSERT #TempStudentCompletion (AcademicYearID, OGP_StudentID, StudentCompletionID)
	SELECT 
		t.AcademicYearID,
		t.OGP_StudentID,
		(CASE WHEN CountCompletionID_1 > 0 THEN 1 ELSE --cont
			(CASE WHEN CountCompletionID_2 > 0 THEN 2 ELSE --complete
				(CASE WHEN CountCompletionID_3 > 0 THEN 3 ELSE --w/drawn
					(CASE WHEN CountCompletionID_6 > 0 THEN 6 ELSE --temp w/drawn
						(CASE WHEN CountCompletionID_4 > 0 THEN 4 ELSE --Xfer
						1
						END) -- Xfer
					END)
				END)
			END)
		END) AS StudentCompletionID 
	FROM 
	(
		SELECT
			OGP_Student.AcademicYearID,
			OGP_Student.ID AS OGP_StudentID, 
			(SELECT Count(E.OverallCompletionID) FROM OGP_Enrolment E WHERE E.OverallCompletionID = 1 AND E.OGP_StudentID = OGP_Student.ID) AS CountCompletionID_1,
			(SELECT Count(E.OverallCompletionID) FROM OGP_Enrolment E WHERE E.OverallCompletionID = 2 AND E.OGP_StudentID = OGP_Student.ID) AS CountCompletionID_2,
			(SELECT Count(E.OverallCompletionID) FROM OGP_Enrolment E WHERE E.OverallCompletionID = 3 AND E.OGP_StudentID = OGP_Student.ID) AS CountCompletionID_3,
			(SELECT Count(E.OverallCompletionID) FROM OGP_Enrolment E WHERE E.OverallCompletionID = 4 AND E.OGP_StudentID = OGP_Student.ID) AS CountCompletionID_4,
			(SELECT Count(E.OverallCompletionID) FROM OGP_Enrolment E WHERE E.OverallCompletionID = 6 AND E.OGP_StudentID = OGP_Student.ID) AS CountCompletionID_6
		FROM
			OGP_Student
			INNER JOIN Delta_Watchlist
				ON Delta_Watchlist.OGP_StudentID = OGP_Student.ID
				AND Delta_Watchlist.StaffID = @StaffID
		WHERE 
			OGP_Student.AcademicYearID = @AcademicYearID
			AND OGP_Student.RecordStatus <> 'Obsolete'
			
	) t

	INSERT #Temp_DeltaScore_Overall (AcademicYearID, OGP_StudentID, DeltaScore_Overall, DeltaScore_Overall_Trend, Latest_Data_Date)
	SELECT 
		AcademicYearID,
		OGP_StudentID,
		Latest_Data_Score,
		Latest_Data_Score - TrendStartScore AS DeltaScore_Overall_Trend,
		Latest_Data_Date
		FROM
		(
		SELECT DISTINCT
			OGP_Enrolment.AcademicYearID,
			OGP_Student.ID AS OGP_StudentID,
			Delta_Student_Score.Latest_Data_Score,
			--Delta_Student_Score.Latest_Data_Week - @NumWeeksTrend AS TrendStartWeekNum,
			CASE (Delta_Student_Score.Latest_Data_Week - @NumWeeksTrend)
				WHEN 1 THEN Wk1_Score_Overall
				WHEN 2 THEN Wk2_Score_Overall
				WHEN 3 THEN Wk3_Score_Overall
				WHEN 4 THEN Wk4_Score_Overall
				WHEN 5 THEN Wk5_Score_Overall
				WHEN 6 THEN Wk6_Score_Overall
				WHEN 7 THEN Wk7_Score_Overall
				WHEN 8 THEN Wk8_Score_Overall
				WHEN 9 THEN Wk9_Score_Overall
				WHEN 10 THEN Wk10_Score_Overall
				WHEN 11 THEN Wk11_Score_Overall
				WHEN 12 THEN Wk12_Score_Overall
				WHEN 13 THEN Wk13_Score_Overall
				WHEN 14 THEN Wk14_Score_Overall
				WHEN 15 THEN Wk15_Score_Overall
				WHEN 16 THEN Wk16_Score_Overall
				WHEN 17 THEN Wk17_Score_Overall
				WHEN 18 THEN Wk18_Score_Overall
				WHEN 19 THEN Wk19_Score_Overall
				WHEN 20 THEN Wk20_Score_Overall
				WHEN 21 THEN Wk21_Score_Overall
				WHEN 22 THEN Wk22_Score_Overall
				WHEN 23 THEN Wk23_Score_Overall
				WHEN 24 THEN Wk24_Score_Overall
				WHEN 25 THEN Wk25_Score_Overall
				WHEN 26 THEN Wk26_Score_Overall
				WHEN 27 THEN Wk27_Score_Overall
				WHEN 28 THEN Wk28_Score_Overall
				WHEN 29 THEN Wk29_Score_Overall
				WHEN 30 THEN Wk30_Score_Overall
				WHEN 31 THEN Wk31_Score_Overall
				WHEN 32 THEN Wk32_Score_Overall
				WHEN 33 THEN Wk33_Score_Overall
				WHEN 34 THEN Wk34_Score_Overall
				WHEN 35 THEN Wk35_Score_Overall
				WHEN 36 THEN Wk36_Score_Overall
				WHEN 37 THEN Wk37_Score_Overall
				WHEN 38 THEN Wk38_Score_Overall
				WHEN 39 THEN Wk39_Score_Overall
				WHEN 40 THEN Wk40_Score_Overall
				WHEN 41 THEN Wk41_Score_Overall
				WHEN 42 THEN Wk42_Score_Overall
				WHEN 43 THEN Wk43_Score_Overall
				WHEN 44 THEN Wk44_Score_Overall
				WHEN 45 THEN Wk45_Score_Overall
				WHEN 46 THEN Wk46_Score_Overall
				WHEN 47 THEN Wk47_Score_Overall
				WHEN 48 THEN Wk48_Score_Overall
				WHEN 49 THEN Wk49_Score_Overall
				WHEN 50 THEN Wk50_Score_Overall
				WHEN 51 THEN Wk51_Score_Overall
				WHEN 52 THEN Wk52_Score_Overall
				WHEN 53 THEN Wk53_Score_Overall
			ELSE Wk1_Score_Overall END AS TrendStartScore, --If the difference ever drops below 1, just use wk1
			Delta_Student_Score.Latest_Data_Date
		FROM
			OGP_Student
				INNER JOIN OGP_Enrolment
				ON OGP_Student.ID = OGP_Enrolment.OGP_StudentID
					INNER JOIN Delta_Watchlist
					ON Delta_Watchlist.OGP_StudentID = OGP_Student.ID
					AND Delta_Watchlist.StaffID = @StaffID
						LEFT JOIN Delta_Student_Score
						ON Delta_Student_Score.OGP_StudentID = OGP_Student.ID
		WHERE 
			OGP_Enrolment.AcademicYearID = @AcademicYearID
			AND OGP_Student.RecordStatus <> 'Obsolete' AND OGP_Enrolment.RecordStatus <> 'Obsolete'
		) t

	INSERT #Temp_DeltaScore_ByCategory (AcademicYearID, OGP_StudentID, DeltaCategoryID, DisplayName, DeltaScore, DeltaValue, DeltaScore_Trend)
	
	SELECT 
	AcademicYearID,
	OGP_StudentID,
	DeltaCategoryID,
	DisplayName,
	Latest_Data_Score,
	Latest_Data_Value,
	Latest_Data_Score - TrendStartScore AS DeltaScore_Trend
	FROM
		(
		SELECT DISTINCT
			OGP_Enrolment.AcademicYearID,
			OGP_Student.ID AS OGP_StudentID,
			Delta_Student_Stat.DeltaCategoryID,
			Delta_Category.DisplayName,
			Delta_Student_Stat.Latest_Data_Score,
			Delta_Student_Stat.Latest_Data_Value,
			CASE (Delta_Student_Stat.Latest_Data_Week - @NumWeeksTrend)
				WHEN 1 THEN Wk1_Score
				WHEN 2 THEN Wk2_Score
				WHEN 3 THEN Wk3_Score
				WHEN 4 THEN Wk4_Score
				WHEN 5 THEN Wk5_Score
				WHEN 6 THEN Wk6_Score
				WHEN 7 THEN Wk7_Score
				WHEN 8 THEN Wk8_Score
				WHEN 9 THEN Wk9_Score
				WHEN 10 THEN Wk10_Score
				WHEN 11 THEN Wk11_Score
				WHEN 12 THEN Wk12_Score
				WHEN 13 THEN Wk13_Score
				WHEN 14 THEN Wk14_Score
				WHEN 15 THEN Wk15_Score
				WHEN 16 THEN Wk16_Score
				WHEN 17 THEN Wk17_Score
				WHEN 18 THEN Wk18_Score
				WHEN 19 THEN Wk19_Score
				WHEN 20 THEN Wk20_Score
				WHEN 21 THEN Wk21_Score
				WHEN 22 THEN Wk22_Score
				WHEN 23 THEN Wk23_Score
				WHEN 24 THEN Wk24_Score
				WHEN 25 THEN Wk25_Score
				WHEN 26 THEN Wk26_Score
				WHEN 27 THEN Wk27_Score
				WHEN 28 THEN Wk28_Score
				WHEN 29 THEN Wk29_Score
				WHEN 30 THEN Wk30_Score
				WHEN 31 THEN Wk31_Score
				WHEN 32 THEN Wk32_Score
				WHEN 33 THEN Wk33_Score
				WHEN 34 THEN Wk34_Score
				WHEN 35 THEN Wk35_Score
				WHEN 36 THEN Wk36_Score
				WHEN 37 THEN Wk37_Score
				WHEN 38 THEN Wk38_Score
				WHEN 39 THEN Wk39_Score
				WHEN 40 THEN Wk40_Score
				WHEN 41 THEN Wk41_Score
				WHEN 42 THEN Wk42_Score
				WHEN 43 THEN Wk43_Score
				WHEN 44 THEN Wk44_Score
				WHEN 45 THEN Wk45_Score
				WHEN 46 THEN Wk46_Score
				WHEN 47 THEN Wk47_Score
				WHEN 48 THEN Wk48_Score
				WHEN 49 THEN Wk49_Score
				WHEN 50 THEN Wk50_Score
				WHEN 51 THEN Wk51_Score
				WHEN 52 THEN Wk52_Score
				WHEN 53 THEN Wk53_Score
			ELSE Wk1_Score END AS TrendStartScore --If the difference ever drops below 1, just use wk1

		FROM
			OGP_Student
				INNER JOIN OGP_Enrolment
				ON OGP_Student.ID = OGP_Enrolment.OGP_StudentID
					INNER JOIN Delta_Watchlist
					ON Delta_Watchlist.OGP_StudentID = OGP_Student.ID
					AND Delta_Watchlist.StaffID = @StaffID
						LEFT JOIN Delta_Student_Stat
						ON Delta_Student_Stat.OGP_StudentID = OGP_Student.ID
							LEFT JOIN Delta_Category
							ON Delta_Student_Stat.DeltaCategoryID = Delta_Category.ID
							AND Delta_Category.IsActive = 1
		WHERE 
			OGP_Enrolment.AcademicYearID = @AcademicYearID
			AND OGP_Student.RecordStatus <> 'Obsolete' AND OGP_Enrolment.RecordStatus <> 'Obsolete'
		) t

END

SELECT 
	OGP_StudentID,
	[Attendance] AS [Attendance_Value], 
	[Punctuality] AS [Punctuality_Value], 
	[Progress] AS [Progress_Value] ,
	[WorkOutstanding] AS [WorkOutstanding_Value],
	[LateStart] AS [LateStart_Value],
	[ExceptionalEntry] AS [ExceptionalEntry_Value],
	[StudentSurvey] AS [StudentSurvey_Value],
	[WorkPlacement] AS [WorkPlacement_Value],
	[Enrichment] AS [Enrichment_Value], 
	[Comments] AS [Comments_Value],
	[Behavioural] AS [Behavioural_Value],
	[Skills] AS [Skills_Value],
	[User1] AS [User1_Value],
	[User2] AS [User2_Value],
	[User3] AS [User3_Value],
	[User4] AS [User4_Value],
	[User5] AS [User5_Value],
	[User6] AS [User6_Value],
	[User7] AS [User7_Value],
	[User8] AS [User8_Value]

INTO #TempPivot_Value
FROM  
	(
	  SELECT 
		  Delta_Category.SystemName AS DeltaSystemName, 
		  #Temp_DeltaScore_ByCategory.AcademicYearID,
		  #Temp_DeltaScore_ByCategory.[OGP_StudentID], 
		  #Temp_DeltaScore_ByCategory.DeltaValue   
	  FROM 
		#Temp_DeltaScore_ByCategory
		  LEFT JOIN Delta_Category
		  ON Delta_Category.ID = #Temp_DeltaScore_ByCategory.DeltaCategoryID
	) AS SourceTable  
	PIVOT  
	(  
	  MAX(DeltaValue)--There should only be one value, but we need MAX, MIN, SUM, COUNT or something.
	  FOR DeltaSystemName IN ([Attendance], [Punctuality], [Progress], [WorkOutstanding], 
								[LateStart], [ExceptionalEntry], [StudentSurvey], [WorkPlacement], 
								[Enrichment], [Comments], [Behavioural], [Skills],
								[User1], [User2], [User3], [User4],
								[User5], [User6], [User7], [User8])  --we need to pull out all 20!
	) AS PivotTable

	
SELECT 
	OGP_StudentID,
	[Attendance] AS [Attendance_Score], 
	[Punctuality] AS [Punctuality_Score], 
	[Progress] AS [Progress_Score],
	[WorkOutstanding] AS [WorkOutstanding_Score],
	[LateStart] AS [LateStart_Score],
	[ExceptionalEntry] AS [ExceptionalEntry_Score],
	[StudentSurvey] AS [StudentSurvey_Score],
	[WorkPlacement] AS [WorkPlacement_Score],
	[Enrichment] AS [Enrichment_Score], 
	[Comments] AS [Comments_Score],
	[Behavioural] AS [Behavioural_Score],
	[Skills] AS [Skills_Score],
	[User1] AS [User1_Score],
	[User2] AS [User2_Score],
	[User3] AS [User3_Score],
	[User4] AS [User4_Score],
	[User5] AS [User5_Score],
	[User6] AS [User6_Score],
	[User7] AS [User7_Score],
	[User8] AS [User8_Score]

INTO #TempPivot_Score
FROM  
	(
	  SELECT 
		  Delta_Category.SystemName AS DeltaSystemName, 
		  #Temp_DeltaScore_ByCategory.AcademicYearID,
		  #Temp_DeltaScore_ByCategory.[OGP_StudentID], 
		  #Temp_DeltaScore_ByCategory.DeltaScore
	  FROM 
		#Temp_DeltaScore_ByCategory
		  LEFT JOIN Delta_Category
		  ON Delta_Category.ID = #Temp_DeltaScore_ByCategory.DeltaCategoryID
	) AS SourceTable  
	PIVOT  
	(  
	  MAX(DeltaScore)--There should only be one value, but we need MAX, MIN, SUM, COUNT or something.
	  FOR DeltaSystemName IN ([Attendance], [Punctuality], [Progress], [WorkOutstanding], 
								[LateStart], [ExceptionalEntry], [StudentSurvey], [WorkPlacement], 
								[Enrichment], [Comments], [Behavioural], [Skills],
								[User1], [User2], [User3], [User4],
								[User5], [User6], [User7], [User8])  --we need to pull out all 20!)  --we need to pull out all 20!
	) AS PivotTable

SELECT 
	OGP_StudentID,
	[Attendance] AS [Attendance_ScoreTrend], 
	[Punctuality] AS [Punctuality_ScoreTrend], 
	[Progress] AS [Progress_ScoreTrend] ,
	[WorkOutstanding] AS [WorkOutstanding_ScoreTrend],
	[LateStart] AS [LateStart_ScoreTrend],
	[ExceptionalEntry] AS [ExceptionalEntry_ScoreTrend],
	[StudentSurvey] AS [StudentSurvey_ScoreTrend],
	[WorkPlacement] AS [WorkPlacement_ScoreTrend],
	[Enrichment] AS [Enrichment_ScoreTrend], 
	[Comments] AS [Comments_ScoreTrend],
	[Behavioural] AS [Behavioural_ScoreTrend],
	[Skills] AS [Skills_ScoreTrend],
	[User1] AS [User1_ScoreTrend],
	[User2] AS [User2_ScoreTrend],
	[User3] AS [User3_ScoreTrend],
	[User4] AS [User4_ScoreTrend],
	[User5] AS [User5_ScoreTrend],
	[User6] AS [User6_ScoreTrend],
	[User7] AS [User7_ScoreTrend],
	[User8] AS [User8_ScoreTrend]

INTO #TempPivot_ScoreTrend
FROM  
	(
	  SELECT 
		  Delta_Category.SystemName AS DeltaSystemName, 
		  #Temp_DeltaScore_ByCategory.AcademicYearID,
		  #Temp_DeltaScore_ByCategory.[OGP_StudentID], 
		  #Temp_DeltaScore_ByCategory.DeltaScore_Trend
	  FROM 
		#Temp_DeltaScore_ByCategory
		  LEFT JOIN Delta_Category
		  ON Delta_Category.ID = #Temp_DeltaScore_ByCategory.DeltaCategoryID
	) AS SourceTable  
	PIVOT  
	(  
	  MAX(DeltaScore_Trend)--There should only be one value, but we need MAX, MIN, SUM, COUNT or something.
	  FOR DeltaSystemName IN ([Attendance], [Punctuality], [Progress], [WorkOutstanding], 
								[LateStart], [ExceptionalEntry], [StudentSurvey], [WorkPlacement], 
								[Enrichment], [Comments], [Behavioural], [Skills],
								[User1], [User2], [User3], [User4],
								[User5], [User6], [User7], [User8])  --we need to pull out all 20!)  --we need to pull out all 20!
	) AS PivotTable


IF @ApplyStaffGroups = 0
BEGIN

  SELECT
	OGP_Student.ID,
	OGP_Student.StudentRef,
	OGP_Student.Forenames,
	OGP_Student.Surname,
	(SELECT StudentCompletionID FROM #TempStudentCompletion WHERE #TempStudentCompletion.OGP_StudentID = OGP_Student.ID) AS CompletionID,
	(SELECT Completion.[Description] FROM #TempStudentCompletion INNER JOIN Completion ON #TempStudentCompletion.StudentCompletionID = Completion.ID WHERE #TempStudentCompletion.OGP_StudentID = OGP_Student.ID) AS CompletionStatus,
	OGP_Student.DOB,
	[Gender],
	(SELECT [Definition] + ' (' + Code + ')' FROM Ethnicity WHERE Ethnicity.ID = OGP_Student.[EthnicityID]) AS Ethnicity,
	(SELECT [Definition] FROM LLDDandHealthProblem WHERE LLDDandHealthProblem.ID = OGP_Student.LLDDandHealthProblemID) AS LLDDAndHealthProblem,
	OGP_Student.FreeSchoolMeal,
	OGP_Student.[UserDefinedString1],
	OGP_Student.[UserDefinedString2],
	OGP_Student.[UserDefinedString3],
	OGP_Student.[UserDefinedString4],
	OGP_Student.[UserDefinedString5],
	OGP_Student.[UserDefinedString6],
	OGP_Student.[UserDefinedString7],
	OGP_Student.[UserDefinedString8],
	OGP_Student.[UserDefinedNumber],
	OGP_Student.[UserDefinedNumber2],
	OGP_Student.[UserDefinedDate],
	StudentFlagValue1, StudentFlagValue2, StudentFlagValue3, StudentFlagValue4, StudentFlagValue5, StudentFlagValue6, 
	StudentFlagValue7, StudentFlagValue8, StudentFlagValue9, StudentFlagValue10, StudentFlagValue11, StudentFlagValue12,
	StudentFlagValue13, StudentFlagValue14, StudentFlagValue15, StudentFlagValue16, StudentFlagValue17, StudentFlagValue18,
	#Temp_DeltaScore_Overall.DeltaScore_Overall,
	#Temp_DeltaScore_Overall.DeltaScore_Overall_Trend,
	#Temp_DeltaScore_Overall.Latest_Data_Date,
	#TempPivot_Value.[Attendance_Value], [Punctuality_Value], [Progress_Value], #TempPivot_Value.[WorkOutstanding_Value],
	#TempPivot_Value.[LateStart_Value], #TempPivot_Value.[ExceptionalEntry_Value], #TempPivot_Value.[StudentSurvey_Value], #TempPivot_Value.[WorkPlacement_Value],
	#TempPivot_Value.[Enrichment_Value], #TempPivot_Value.[Comments_Value], #TempPivot_Value.[Behavioural_Value], #TempPivot_Value.[Skills_Value],
	#TempPivot_Value.[User1_Value], #TempPivot_Value.[User2_Value], #TempPivot_Value.[User3_Value], #TempPivot_Value.[User4_Value],
	#TempPivot_Value.[User5_Value], #TempPivot_Value.[User6_Value], #TempPivot_Value.[User7_Value], #TempPivot_Value.[User8_Value],

	#TempPivot_Score.[Attendance_Score], #TempPivot_Score.[Punctuality_Score], #TempPivot_Score.[Progress_Score], #TempPivot_Score.[WorkOutstanding_Score], --we need to pull out all 20!
	#TempPivot_Score.[LateStart_Score], #TempPivot_Score.[ExceptionalEntry_Score], #TempPivot_Score.[StudentSurvey_Score], #TempPivot_Score.[WorkPlacement_Score],
	#TempPivot_Score.[Enrichment_Score], #TempPivot_Score.[Comments_Score], #TempPivot_Score.[Behavioural_Score], #TempPivot_Score.[Skills_Score],
	#TempPivot_Score.[User1_Score], #TempPivot_Score.[User2_Score], #TempPivot_Score.[User3_Score], #TempPivot_Score.[User4_Score],
	#TempPivot_Score.[User5_Score], #TempPivot_Score.[User6_Score], #TempPivot_Score.[User7_Score], #TempPivot_Score.[User8_Score],

	#TempPivot_ScoreTrend.[Attendance_ScoreTrend], #TempPivot_ScoreTrend.[Punctuality_ScoreTrend], #TempPivot_ScoreTrend.[Progress_ScoreTrend], #TempPivot_ScoreTrend.[WorkOutstanding_ScoreTrend],
	#TempPivot_ScoreTrend.[LateStart_ScoreTrend], #TempPivot_ScoreTrend.[ExceptionalEntry_ScoreTrend], #TempPivot_ScoreTrend.[StudentSurvey_ScoreTrend], #TempPivot_ScoreTrend.[WorkPlacement_ScoreTrend],
	#TempPivot_ScoreTrend.[Enrichment_ScoreTrend], #TempPivot_ScoreTrend.[Comments_ScoreTrend], #TempPivot_ScoreTrend.[Behavioural_ScoreTrend], #TempPivot_ScoreTrend.[Skills_ScoreTrend],
	#TempPivot_ScoreTrend.[User1_ScoreTrend], #TempPivot_ScoreTrend.[User2_ScoreTrend], #TempPivot_ScoreTrend.[User3_ScoreTrend], #TempPivot_ScoreTrend.[User4_ScoreTrend],
	#TempPivot_ScoreTrend.[User5_ScoreTrend], #TempPivot_ScoreTrend.[User6_ScoreTrend], #TempPivot_ScoreTrend.[User7_ScoreTrend], #TempPivot_ScoreTrend.[User8_ScoreTrend],

	CASE WHEN Delta_Watchlist.OGP_StudentID = OGP_Student.ID THEN 1 ELSE 0 END AS IsInWatchlist
  FROM
	#Temp_DeltaScore_Overall
	INNER JOIN OGP_Student 	ON #Temp_DeltaScore_Overall.OGP_StudentID = OGP_Student.ID
		INNER JOIN #TempPivot_Value 		ON #Temp_DeltaScore_Overall.OGP_StudentID = #TempPivot_Value.OGP_StudentID
			INNER JOIN #TempPivot_Score 			ON #Temp_DeltaScore_Overall.OGP_StudentID = #TempPivot_Score.OGP_StudentID 
				INNER JOIN #TempPivot_ScoreTrend 				ON #Temp_DeltaScore_Overall.OGP_StudentID = #TempPivot_ScoreTrend.OGP_StudentID  
					LEFT JOIN Delta_Student_InformationFlag 					ON Delta_Student_InformationFlag.OGP_StudentID = #Temp_DeltaScore_Overall.OGP_StudentID
						LEFT JOIN Delta_Watchlist 							ON OGP_Student.ID = Delta_Watchlist.OGP_StudentID 							AND Delta_Watchlist.StaffID = @StaffID

END
ELSE
BEGIN

  SELECT
	OGP_Student.ID,
	OGP_Student.StudentRef,
	OGP_Student.Forenames,
	OGP_Student.Surname,
	(SELECT StudentCompletionID FROM #TempStudentCompletion WHERE #TempStudentCompletion.OGP_StudentID = OGP_Student.ID) AS CompletionID,
	(SELECT Completion.[Description] FROM #TempStudentCompletion INNER JOIN Completion ON #TempStudentCompletion.StudentCompletionID = Completion.ID WHERE #TempStudentCompletion.OGP_StudentID = OGP_Student.ID) AS CompletionStatus,
	OGP_Student.DOB,
	[Gender],
	(SELECT [Definition] + ' (' + Code + ')' FROM Ethnicity WHERE Ethnicity.ID = OGP_Student.[EthnicityID]) AS Ethnicity,
	(SELECT [Definition] FROM LLDDandHealthProblem WHERE LLDDandHealthProblem.ID = OGP_Student.LLDDandHealthProblemID) AS LLDDAndHealthProblem,
	OGP_Student.FreeSchoolMeal,
	OGP_Student.[UserDefinedString1],
	OGP_Student.[UserDefinedString2],
	OGP_Student.[UserDefinedString3],
	OGP_Student.[UserDefinedString4],
	OGP_Student.[UserDefinedString5],
	OGP_Student.[UserDefinedString6],
	OGP_Student.[UserDefinedString7],
	OGP_Student.[UserDefinedString8],
	OGP_Student.[UserDefinedNumber],
	OGP_Student.[UserDefinedNumber2],
	OGP_Student.[UserDefinedDate],
	StudentFlagValue1, StudentFlagValue2, StudentFlagValue3, StudentFlagValue4, StudentFlagValue5, StudentFlagValue6, 
	StudentFlagValue7, StudentFlagValue8, StudentFlagValue9, StudentFlagValue10, StudentFlagValue11, StudentFlagValue12,
	StudentFlagValue13, StudentFlagValue14, StudentFlagValue15, StudentFlagValue16, StudentFlagValue17, StudentFlagValue18,
	#Temp_DeltaScore_Overall.DeltaScore_Overall,
	#Temp_DeltaScore_Overall.DeltaScore_Overall_Trend,
	#Temp_DeltaScore_Overall.Latest_Data_Date,
	#TempPivot_Value.[Attendance_Value], [Punctuality_Value], [Progress_Value], #TempPivot_Value.[WorkOutstanding_Value],
	#TempPivot_Value.[LateStart_Value], #TempPivot_Value.[ExceptionalEntry_Value], #TempPivot_Value.[StudentSurvey_Value], #TempPivot_Value.[WorkPlacement_Value],
	#TempPivot_Value.[Enrichment_Value], #TempPivot_Value.[Comments_Value], #TempPivot_Value.[Behavioural_Value], #TempPivot_Value.[Skills_Value],
	#TempPivot_Value.[User1_Value], #TempPivot_Value.[User2_Value], #TempPivot_Value.[User3_Value], #TempPivot_Value.[User4_Value],
	#TempPivot_Value.[User5_Value], #TempPivot_Value.[User6_Value], #TempPivot_Value.[User7_Value], #TempPivot_Value.[User8_Value],

	#TempPivot_Score.[Attendance_Score], #TempPivot_Score.[Punctuality_Score], #TempPivot_Score.[Progress_Score], #TempPivot_Score.[WorkOutstanding_Score], --we need to pull out all 20!
	#TempPivot_Score.[LateStart_Score], #TempPivot_Score.[ExceptionalEntry_Score], #TempPivot_Score.[StudentSurvey_Score], #TempPivot_Score.[WorkPlacement_Score],
	#TempPivot_Score.[Enrichment_Score], #TempPivot_Score.[Comments_Score], #TempPivot_Score.[Behavioural_Score], #TempPivot_Score.[Skills_Score],
	#TempPivot_Score.[User1_Score], #TempPivot_Score.[User2_Score], #TempPivot_Score.[User3_Score], #TempPivot_Score.[User4_Score],
	#TempPivot_Score.[User5_Score], #TempPivot_Score.[User6_Score], #TempPivot_Score.[User7_Score], #TempPivot_Score.[User8_Score],

	#TempPivot_ScoreTrend.[Attendance_ScoreTrend], #TempPivot_ScoreTrend.[Punctuality_ScoreTrend], #TempPivot_ScoreTrend.[Progress_ScoreTrend], #TempPivot_ScoreTrend.[WorkOutstanding_ScoreTrend],
	#TempPivot_ScoreTrend.[LateStart_ScoreTrend], #TempPivot_ScoreTrend.[ExceptionalEntry_ScoreTrend], #TempPivot_ScoreTrend.[StudentSurvey_ScoreTrend], #TempPivot_ScoreTrend.[WorkPlacement_ScoreTrend],
	#TempPivot_ScoreTrend.[Enrichment_ScoreTrend], #TempPivot_ScoreTrend.[Comments_ScoreTrend], #TempPivot_ScoreTrend.[Behavioural_ScoreTrend], #TempPivot_ScoreTrend.[Skills_ScoreTrend],
	#TempPivot_ScoreTrend.[User1_ScoreTrend], #TempPivot_ScoreTrend.[User2_ScoreTrend], #TempPivot_ScoreTrend.[User3_ScoreTrend], #TempPivot_ScoreTrend.[User4_ScoreTrend],
	#TempPivot_ScoreTrend.[User5_ScoreTrend], #TempPivot_ScoreTrend.[User6_ScoreTrend], #TempPivot_ScoreTrend.[User7_ScoreTrend], #TempPivot_ScoreTrend.[User8_ScoreTrend],

	CASE WHEN Delta_Watchlist.OGP_StudentID = OGP_Student.ID THEN 1 ELSE 0 END AS IsInWatchlist
  FROM
	#Temp_DeltaScore_Overall 	
	  INNER JOIN OGP_Student 	ON #Temp_DeltaScore_Overall.OGP_StudentID = OGP_Student.ID
		INNER JOIN #TempPivot_Value		ON #Temp_DeltaScore_Overall.OGP_StudentID = #TempPivot_Value.OGP_StudentID
			INNER JOIN #TempPivot_Score			ON #Temp_DeltaScore_Overall.OGP_StudentID = #TempPivot_Score.OGP_StudentID 
				INNER JOIN #TempPivot_ScoreTrend				ON #Temp_DeltaScore_Overall.OGP_StudentID = #TempPivot_ScoreTrend.OGP_StudentID  
					LEFT JOIN Delta_Student_InformationFlag					ON Delta_Student_InformationFlag.OGP_StudentID = #Temp_DeltaScore_Overall.OGP_StudentID
						LEFT JOIN Delta_Watchlist							ON OGP_Student.ID = Delta_Watchlist.OGP_StudentID AND Delta_Watchlist.StaffID = @StaffID

  WHERE     -- This restricts the Students to those in 'My Groups' 
    #Temp_DeltaScore_Overall.OGP_StudentID IN
	(
      SELECT 
        OGP_Enrolment.OGP_StudentID           --OGP_Group.OGP_GroupCode, OGP_Enrolment.StudentRef, OGP_Enrolment.OGP_StudentID, OGP_GroupEnrolment.* 
      FROM 
	    OGP_Enrolment
          INNER JOIN OGP_GroupEnrolment ON OGP_Enrolment.ID = OGP_GroupEnrolment.OGP_EnrolmentID
            INNER JOIN OGP_Group ON OGP_Group.ID = OGP_GroupEnrolment.OGP_GroupID
              INNER JOIN OGP_GroupStaff ON OGP_Group.ID = OGP_GroupStaff.OGP_GroupID
      WHERE 
        OGP_GroupStaff.StaffID = @StaffID AND 
		OGP_Group.AcademicYearID = @AcademicYearID
		AND OGP_Enrolment.RecordStatus <> 'Obsolete' AND OGP_GroupEnrolment.RecordStatus <> 'Obsolete'  AND OGP_Group.RecordStatus <> 'Obsolete' 
		 AND OGP_GroupStaff.RecordStatus <> 'Obsolete' 
    )
END

GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_WebPlus_Delta_StudentScores_Select] 
  @ID             AS VARCHAR(10), 
  @GroupingType   AS VARCHAR(20), 
  @AcademicYearID AS VARCHAR(5), 
  @NumWeeksTrend  AS INT, 
  @StaffID        AS INT
AS

-- =============================================
-- Author:		Andrew Genner
-- Create date: 17/07/2023
-- Modified:    03/09/2023 AndyB Added GroupingType=Student
-- Modfied:		08/09/2023 AMG Added the full set of Delta Categories and the CompletionID
-- Modified:	14/09/2023 AMG - The Trend was being calculated the wrong way around - corrected.
-- Modified:	14/09/2023 AndyB - Added Latest_Data_Week to the output table
-- Modified:    26/09/2023 AMG - Added mode STUDENTVIEW,so Students see only delta categories that are ContributeToStudentScore = 1
-- Modified:	20/10/2023 AMG - Changed Delta Category PTWorkHours to StudentSurvey
-- Description:	This is to display the OneGrade Plus Delta Scores
-- Pass in the ID and the Grouping Type (Group, Course, LearningAimRef, CollegeStructure)
-- An Overall Student Score is calculated - as is a score for each delta category (and value)
-- It is then joined with Student and StudentFlag information
-- The trend is calculated depending on how many weeks back we want to look - passed as the @NumWeekTrend parameter.
-- Modified:	15/11/2023 AMG - LEFT JOIN to DELTA_CATEGORY for Student and StudentView
-- Modified:	16/11/2023 AMG - When populating #TempStudentCompletion, it now SUMS And GROUPS the CompStatus for the student - otherwise we get multiple recs for the student.
-- Modified:	06/12/2023 AMG - Added in <> 'Obsolete'
-- Modified:	16/01/2024 AMG - In the Course View, there was a hardcoded 2 instead of the @@NumWeeksTrend - fixed
-- Modified:	16/01/2023 AMG Added StudentFlagValue13-18, Changeed Delta Category EmployabilitySkills to Skills
------------------------------------------------
-------------------
--Remember, Group, Course and CollegeStructure will be passed @ID as an integer, so this is CAST as an integer when used
--LearningAimRef is passed @ID as a varchar
-------------------
--exec sp_WebPlus_Delta_StudentScores_Select '576', 'Group', '19/20', 5, 2020
--exec sp_WebPlus_Delta_StudentScores_Select '2790', 'Course', '23/24',5, 2020
--exec sp_WebPlus_Delta_StudentScores_Select '50053164', 'LearningAimRef', '19/20', 4, 2020
--exec sp_WebPlus_Delta_StudentScores_Select '3', 'CollegeStructure', '19/20', 6, 2020
--exec sp_WebPlus_Delta_StudentScores_Select '30147759', 'STUDENT', '19/20', 4, 2020


--Group 1469

CREATE TABLE #Temp_DeltaScore_Overall (
	AcademicYearID varchar(5) NOT NULL,
	OGP_StudentID INT NOT NULL,
	DeltaScore_Overall INT NULL,
	DeltaScore_Overall_Trend INT NULL,
	Latest_Data_Week INT NULL,
	Latest_Data_Date DATETIME NULL
					)

CREATE TABLE #Temp_DeltaScore_ByCategory (
	AcademicYearID varchar(5) NOT NULL,
	OGP_StudentID INT NOT NULL,
	DeltaCategoryID INT NULL,
	DisplayName VARCHAR(100) NULL,
	DeltaScore INT NULL,
	DeltaValue DECIMAL(19,2) NULL,
	DeltaScore_Trend INT NULL
					)

CREATE TABLE #TempStudentCompletion (
	AcademicYearID varchar(5) NOT NULL,
	OGP_StudentID INT NOT NULL,
	StudentCompletionID INT NOT NULL
					)

--We have a Group - OGP_Group.ID 
--We need to show a list of students related to that group: @GroupID

--DECLARE @ID INT
--SELECT @ID = 576
--DECLARE @AcademicYearID varchar(5)
--SELECT @AcademicYearID = '19/20'
--DECLARE @GroupingType varchar(20)
--SELECT @GroupingType = 'Group'

----------------------------------------------------------
--Populate a #Temp with the information we need to display
----------------------------------------------------------
IF @GroupingType = 'Group'
BEGIN
	--Work out (based on the Enrolments included in the selection), the Completion ID for each Student
	INSERT #TempStudentCompletion (AcademicYearID, OGP_StudentID, StudentCompletionID)
	SELECT 
		t.AcademicYearID,
		t.OGP_StudentID,
		(CASE WHEN CountCompletionID_1 > 0 THEN 1 ELSE --cont
			(CASE WHEN CountCompletionID_2 > 0 THEN 2 ELSE --complete
				(CASE WHEN CountCompletionID_3 > 0 THEN 3 ELSE --w/drawn
					(CASE WHEN CountCompletionID_6 > 0 THEN 6 ELSE --temp w/drawn
						(CASE WHEN CountCompletionID_4 > 0 THEN 4 ELSE --Xfer
						1
						END) -- Xfer
					END)
				END)
			END)
		END) AS StudentCompletionID 
	FROM 
	(
		SELECT
			tt.AcademicYearID, tt.OGP_StudentID, SUM(tt.CountCompletionID_1) AS CountCompletionID_1, SUM(tt.CountCompletionID_2) AS CountCompletionID_2,
			SUM(tt.CountCompletionID_3) AS CountCompletionID_3, SUM(tt.CountCompletionID_4) AS CountCompletionID_4, SUM(tt.CountCompletionID_6) AS CountCompletionID_6
		FROM
		(
			SELECT DISTINCT
				OGP_Enrolment.AcademicYearID,
				OGP_Enrolment.OGP_StudentID, 
				(SELECT Count(E.OverallCompletionID) FROM OGP_Enrolment E WHERE E.OverallCompletionID = 1 AND E.ID = OGP_Enrolment.ID) AS CountCompletionID_1,
				(SELECT Count(E.OverallCompletionID) FROM OGP_Enrolment E WHERE E.OverallCompletionID = 2 AND E.ID = OGP_Enrolment.ID) AS CountCompletionID_2,
				(SELECT Count(E.OverallCompletionID) FROM OGP_Enrolment E WHERE E.OverallCompletionID = 3 AND E.ID = OGP_Enrolment.ID) AS CountCompletionID_3,
				(SELECT Count(E.OverallCompletionID) FROM OGP_Enrolment E WHERE E.OverallCompletionID = 4 AND E.ID = OGP_Enrolment.ID) AS CountCompletionID_4,
				(SELECT Count(E.OverallCompletionID) FROM OGP_Enrolment E WHERE E.OverallCompletionID = 6 AND E.ID = OGP_Enrolment.ID) AS CountCompletionID_6
			FROM
				OGP_Enrolment INNER JOIN OGP_GroupEnrolment ON OGP_GroupEnrolment.OGP_EnrolmentID = OGP_Enrolment.ID
			WHERE 
				OGP_GroupEnrolment.OGP_GroupID = CAST(@ID AS INT) AND OGP_Enrolment.AcademicYearID = @AcademicYearID
				AND OGP_GroupEnrolment.RecordStatus <> 'Obsolete' AND OGP_Enrolment.RecordStatus <> 'Obsolete'
		) tt
		GROUP BY
			tt.AcademicYearID, tt.OGP_StudentID
	) t


	INSERT #Temp_DeltaScore_Overall (AcademicYearID, OGP_StudentID, DeltaScore_Overall, DeltaScore_Overall_Trend, Latest_Data_Date, Latest_Data_Week)
	SELECT 
		AcademicYearID,
		OGP_StudentID,
		Latest_Data_Score,
		Latest_Data_Score - TrendStartScore AS DeltaScore_Overall_Trend,
		Latest_Data_Date,
		Latest_Data_Week
		FROM
		(
		SELECT DISTINCT
			OGP_Enrolment.AcademicYearID,
			OGP_Student.ID AS OGP_StudentID,
			Delta_Student_Score.Latest_Data_Score,
			--Delta_Student_Score.Latest_Data_Week - @NumWeeksTrend AS TrendStartWeekNum,
			CASE (Delta_Student_Score.Latest_Data_Week - @NumWeeksTrend)
				WHEN 1 THEN Wk1_Score_Overall
				WHEN 2 THEN Wk2_Score_Overall
				WHEN 3 THEN Wk3_Score_Overall
				WHEN 4 THEN Wk4_Score_Overall
				WHEN 5 THEN Wk5_Score_Overall
				WHEN 6 THEN Wk6_Score_Overall
				WHEN 7 THEN Wk7_Score_Overall
				WHEN 8 THEN Wk8_Score_Overall
				WHEN 9 THEN Wk9_Score_Overall
				WHEN 10 THEN Wk10_Score_Overall
				WHEN 11 THEN Wk11_Score_Overall
				WHEN 12 THEN Wk12_Score_Overall
				WHEN 13 THEN Wk13_Score_Overall
				WHEN 14 THEN Wk14_Score_Overall
				WHEN 15 THEN Wk15_Score_Overall
				WHEN 16 THEN Wk16_Score_Overall
				WHEN 17 THEN Wk17_Score_Overall
				WHEN 18 THEN Wk18_Score_Overall
				WHEN 19 THEN Wk19_Score_Overall
				WHEN 20 THEN Wk20_Score_Overall
				WHEN 21 THEN Wk21_Score_Overall
				WHEN 22 THEN Wk22_Score_Overall
				WHEN 23 THEN Wk23_Score_Overall
				WHEN 24 THEN Wk24_Score_Overall
				WHEN 25 THEN Wk25_Score_Overall
				WHEN 26 THEN Wk26_Score_Overall
				WHEN 27 THEN Wk27_Score_Overall
				WHEN 28 THEN Wk28_Score_Overall
				WHEN 29 THEN Wk29_Score_Overall
				WHEN 30 THEN Wk30_Score_Overall
				WHEN 31 THEN Wk31_Score_Overall
				WHEN 32 THEN Wk32_Score_Overall
				WHEN 33 THEN Wk33_Score_Overall
				WHEN 34 THEN Wk34_Score_Overall
				WHEN 35 THEN Wk35_Score_Overall
				WHEN 36 THEN Wk36_Score_Overall
				WHEN 37 THEN Wk37_Score_Overall
				WHEN 38 THEN Wk38_Score_Overall
				WHEN 39 THEN Wk39_Score_Overall
				WHEN 40 THEN Wk40_Score_Overall
				WHEN 41 THEN Wk41_Score_Overall
				WHEN 42 THEN Wk42_Score_Overall
				WHEN 43 THEN Wk43_Score_Overall
				WHEN 44 THEN Wk44_Score_Overall
				WHEN 45 THEN Wk45_Score_Overall
				WHEN 46 THEN Wk46_Score_Overall
				WHEN 47 THEN Wk47_Score_Overall
				WHEN 48 THEN Wk48_Score_Overall
				WHEN 49 THEN Wk49_Score_Overall
				WHEN 50 THEN Wk50_Score_Overall
				WHEN 51 THEN Wk51_Score_Overall
				WHEN 52 THEN Wk52_Score_Overall
				WHEN 53 THEN Wk53_Score_Overall
			ELSE Wk1_Score_Overall END AS TrendStartScore, --If the difference ever drops below 1, just use wk1
			Delta_Student_Score.Latest_Data_Date,
			Delta_Student_Score.Latest_Data_Week
		FROM
			OGP_Student
				INNER JOIN OGP_Enrolment
				ON OGP_Student.ID = OGP_Enrolment.OGP_StudentID
					INNER JOIN OGP_GroupEnrolment
					ON OGP_GroupEnrolment.OGP_EnrolmentID = OGP_Enrolment.ID
						LEFT JOIN Delta_Student_Score
						ON Delta_Student_Score.OGP_StudentID = OGP_Student.ID
		WHERE 
			OGP_GroupEnrolment.OGP_GroupID = CAST(@ID AS INT)
			AND OGP_Enrolment.AcademicYearID = @AcademicYearID
			AND OGP_GroupEnrolment.RecordStatus <> 'Obsolete' AND OGP_Enrolment.RecordStatus <> 'Obsolete' AND OGP_Student.RecordStatus <> 'Obsolete'
		) t

	INSERT #Temp_DeltaScore_ByCategory (AcademicYearID, OGP_StudentID, DeltaCategoryID, DisplayName, DeltaScore, DeltaValue, DeltaScore_Trend)
	
	SELECT 
	AcademicYearID,
	OGP_StudentID,
	DeltaCategoryID,
	DisplayName,
	Latest_Data_Score,
	Latest_Data_Value,
	Latest_Data_Score - TrendStartScore AS DeltaScore_Trend
	FROM
		(
		SELECT DISTINCT
			OGP_Enrolment.AcademicYearID,
			OGP_Student.ID AS OGP_StudentID,
			Delta_Student_Stat.DeltaCategoryID,
			Delta_Category.DisplayName,
			Delta_Student_Stat.Latest_Data_Score,
			Delta_Student_Stat.Latest_Data_Value,
			CASE (Delta_Student_Stat.Latest_Data_Week - @NumWeeksTrend)
				WHEN 1 THEN Wk1_Score
				WHEN 2 THEN Wk2_Score
				WHEN 3 THEN Wk3_Score
				WHEN 4 THEN Wk4_Score
				WHEN 5 THEN Wk5_Score
				WHEN 6 THEN Wk6_Score
				WHEN 7 THEN Wk7_Score
				WHEN 8 THEN Wk8_Score
				WHEN 9 THEN Wk9_Score
				WHEN 10 THEN Wk10_Score
				WHEN 11 THEN Wk11_Score
				WHEN 12 THEN Wk12_Score
				WHEN 13 THEN Wk13_Score
				WHEN 14 THEN Wk14_Score
				WHEN 15 THEN Wk15_Score
				WHEN 16 THEN Wk16_Score
				WHEN 17 THEN Wk17_Score
				WHEN 18 THEN Wk18_Score
				WHEN 19 THEN Wk19_Score
				WHEN 20 THEN Wk20_Score
				WHEN 21 THEN Wk21_Score
				WHEN 22 THEN Wk22_Score
				WHEN 23 THEN Wk23_Score
				WHEN 24 THEN Wk24_Score
				WHEN 25 THEN Wk25_Score
				WHEN 26 THEN Wk26_Score
				WHEN 27 THEN Wk27_Score
				WHEN 28 THEN Wk28_Score
				WHEN 29 THEN Wk29_Score
				WHEN 30 THEN Wk30_Score
				WHEN 31 THEN Wk31_Score
				WHEN 32 THEN Wk32_Score
				WHEN 33 THEN Wk33_Score
				WHEN 34 THEN Wk34_Score
				WHEN 35 THEN Wk35_Score
				WHEN 36 THEN Wk36_Score
				WHEN 37 THEN Wk37_Score
				WHEN 38 THEN Wk38_Score
				WHEN 39 THEN Wk39_Score
				WHEN 40 THEN Wk40_Score
				WHEN 41 THEN Wk41_Score
				WHEN 42 THEN Wk42_Score
				WHEN 43 THEN Wk43_Score
				WHEN 44 THEN Wk44_Score
				WHEN 45 THEN Wk45_Score
				WHEN 46 THEN Wk46_Score
				WHEN 47 THEN Wk47_Score
				WHEN 48 THEN Wk48_Score
				WHEN 49 THEN Wk49_Score
				WHEN 50 THEN Wk50_Score
				WHEN 51 THEN Wk51_Score
				WHEN 52 THEN Wk52_Score
				WHEN 53 THEN Wk53_Score
			ELSE Wk1_Score END AS TrendStartScore --If the difference ever drops below 1, just use wk1
		FROM
			OGP_Student
				INNER JOIN OGP_Enrolment
				ON OGP_Student.ID = OGP_Enrolment.OGP_StudentID
					INNER JOIN OGP_GroupEnrolment
					ON OGP_GroupEnrolment.OGP_EnrolmentID = OGP_Enrolment.ID
						LEFT JOIN Delta_Student_Stat
						ON Delta_Student_Stat.OGP_StudentID = OGP_Student.ID
							LEFT JOIN Delta_Category
							ON Delta_Student_Stat.DeltaCategoryID = Delta_Category.ID
							AND Delta_Category.IsActive = 1
		WHERE 
			OGP_GroupEnrolment.OGP_GroupID = CAST(@ID AS INT)
			AND OGP_Enrolment.AcademicYearID = @AcademicYearID
			AND OGP_GroupEnrolment.RecordStatus <> 'Obsolete' AND OGP_Enrolment.RecordStatus <> 'Obsolete' AND OGP_Student.RecordStatus <> 'Obsolete'
		) t

END

IF @GroupingType = 'STUDENT'
BEGIN

	--Work out (based on the Enrolments included in the selection), the Completion ID for each Student
	INSERT #TempStudentCompletion (AcademicYearID, OGP_StudentID, StudentCompletionID)
	SELECT 
		t.AcademicYearID,
		t.OGP_StudentID,
		(CASE WHEN CountCompletionID_1 > 0 THEN 1 ELSE --cont
			(CASE WHEN CountCompletionID_2 > 0 THEN 2 ELSE --complete
				(CASE WHEN CountCompletionID_3 > 0 THEN 3 ELSE --w/drawn
					(CASE WHEN CountCompletionID_6 > 0 THEN 6 ELSE --temp w/drawn
						(CASE WHEN CountCompletionID_4 > 0 THEN 4 ELSE --Xfer
						1
						END) -- Xfer
					END)
				END)
			END)
		END) AS StudentCompletionID 
	FROM 
	(
		SELECT
			tt.AcademicYearID, tt.OGP_StudentID, SUM(tt.CountCompletionID_1) AS CountCompletionID_1, SUM(tt.CountCompletionID_2) AS CountCompletionID_2,
			SUM(tt.CountCompletionID_3) AS CountCompletionID_3, SUM(tt.CountCompletionID_4) AS CountCompletionID_4, SUM(tt.CountCompletionID_6) AS CountCompletionID_6
		FROM
		(
			SELECT DISTINCT
				OGP_Enrolment.AcademicYearID,
				OGP_Enrolment.OGP_StudentID, 
				(SELECT Count(E.OverallCompletionID) FROM OGP_Enrolment E WHERE E.OverallCompletionID = 1 AND E.ID = OGP_Enrolment.ID) AS CountCompletionID_1,
				(SELECT Count(E.OverallCompletionID) FROM OGP_Enrolment E WHERE E.OverallCompletionID = 2 AND E.ID = OGP_Enrolment.ID) AS CountCompletionID_2,
				(SELECT Count(E.OverallCompletionID) FROM OGP_Enrolment E WHERE E.OverallCompletionID = 3 AND E.ID = OGP_Enrolment.ID) AS CountCompletionID_3,
				(SELECT Count(E.OverallCompletionID) FROM OGP_Enrolment E WHERE E.OverallCompletionID = 4 AND E.ID = OGP_Enrolment.ID) AS CountCompletionID_4,
				(SELECT Count(E.OverallCompletionID) FROM OGP_Enrolment E WHERE E.OverallCompletionID = 6 AND E.ID = OGP_Enrolment.ID) AS CountCompletionID_6
			FROM 
				OGP_Student INNER JOIN OGP_Enrolment ON OGP_Student.ID = OGP_Enrolment.OGP_StudentID
			WHERE 
				OGP_Student.StudentRef = @ID AND OGP_Enrolment.AcademicYearID = @AcademicYearID
				AND OGP_Enrolment.RecordStatus <> 'Obsolete' AND OGP_Student.RecordStatus <> 'Obsolete'
		) tt
		GROUP BY
			tt.AcademicYearID, tt.OGP_StudentID
	) t

	INSERT #Temp_DeltaScore_Overall (AcademicYearID, OGP_StudentID, DeltaScore_Overall, DeltaScore_Overall_Trend, Latest_Data_Date, Latest_Data_Week)
	SELECT 
		AcademicYearID,
		OGP_StudentID,
		Latest_Data_Score,
		Latest_Data_Score - TrendStartScore AS DeltaScore_Overall_Trend,
		Latest_Data_Date,
		Latest_Data_Week
		FROM
		(
		SELECT DISTINCT
			OGP_Enrolment.AcademicYearID,
			OGP_Student.ID AS OGP_StudentID,
			Delta_Student_Score.Latest_Data_Score,
			--Delta_Student_Score.Latest_Data_Week - @NumWeeksTrend AS TrendStartWeekNum,
			CASE (Delta_Student_Score.Latest_Data_Week - @NumWeeksTrend)
				WHEN 1 THEN Wk1_Score_Overall
				WHEN 2 THEN Wk2_Score_Overall
				WHEN 3 THEN Wk3_Score_Overall
				WHEN 4 THEN Wk4_Score_Overall
				WHEN 5 THEN Wk5_Score_Overall
				WHEN 6 THEN Wk6_Score_Overall
				WHEN 7 THEN Wk7_Score_Overall
				WHEN 8 THEN Wk8_Score_Overall
				WHEN 9 THEN Wk9_Score_Overall
				WHEN 10 THEN Wk10_Score_Overall
				WHEN 11 THEN Wk11_Score_Overall
				WHEN 12 THEN Wk12_Score_Overall
				WHEN 13 THEN Wk13_Score_Overall
				WHEN 14 THEN Wk14_Score_Overall
				WHEN 15 THEN Wk15_Score_Overall
				WHEN 16 THEN Wk16_Score_Overall
				WHEN 17 THEN Wk17_Score_Overall
				WHEN 18 THEN Wk18_Score_Overall
				WHEN 19 THEN Wk19_Score_Overall
				WHEN 20 THEN Wk20_Score_Overall
				WHEN 21 THEN Wk21_Score_Overall
				WHEN 22 THEN Wk22_Score_Overall
				WHEN 23 THEN Wk23_Score_Overall
				WHEN 24 THEN Wk24_Score_Overall
				WHEN 25 THEN Wk25_Score_Overall
				WHEN 26 THEN Wk26_Score_Overall
				WHEN 27 THEN Wk27_Score_Overall
				WHEN 28 THEN Wk28_Score_Overall
				WHEN 29 THEN Wk29_Score_Overall
				WHEN 30 THEN Wk30_Score_Overall
				WHEN 31 THEN Wk31_Score_Overall
				WHEN 32 THEN Wk32_Score_Overall
				WHEN 33 THEN Wk33_Score_Overall
				WHEN 34 THEN Wk34_Score_Overall
				WHEN 35 THEN Wk35_Score_Overall
				WHEN 36 THEN Wk36_Score_Overall
				WHEN 37 THEN Wk37_Score_Overall
				WHEN 38 THEN Wk38_Score_Overall
				WHEN 39 THEN Wk39_Score_Overall
				WHEN 40 THEN Wk40_Score_Overall
				WHEN 41 THEN Wk41_Score_Overall
				WHEN 42 THEN Wk42_Score_Overall
				WHEN 43 THEN Wk43_Score_Overall
				WHEN 44 THEN Wk44_Score_Overall
				WHEN 45 THEN Wk45_Score_Overall
				WHEN 46 THEN Wk46_Score_Overall
				WHEN 47 THEN Wk47_Score_Overall
				WHEN 48 THEN Wk48_Score_Overall
				WHEN 49 THEN Wk49_Score_Overall
				WHEN 50 THEN Wk50_Score_Overall
				WHEN 51 THEN Wk51_Score_Overall
				WHEN 52 THEN Wk52_Score_Overall
				WHEN 53 THEN Wk53_Score_Overall
			ELSE 
				Wk1_Score_Overall END AS TrendStartScore, --If the difference ever drops below 1, just use wk1
			Delta_Student_Score.Latest_Data_Date,
			Delta_Student_Score.Latest_Data_Week
		FROM
			OGP_Student
				INNER JOIN OGP_Enrolment
					ON OGP_Student.ID = OGP_Enrolment.OGP_StudentID
--					INNER JOIN OGP_GroupEnrolment
--						ON OGP_GroupEnrolment.OGP_EnrolmentID = OGP_Enrolment.ID
				LEFT JOIN Delta_Student_Score
					ON Delta_Student_Score.OGP_StudentID = OGP_Student.ID
		WHERE 
			OGP_Student.StudentRef = @ID AND OGP_Enrolment.AcademicYearID = @AcademicYearID
			AND OGP_Enrolment.RecordStatus <> 'Obsolete' AND OGP_Student.RecordStatus <> 'Obsolete'
		) t

	INSERT #Temp_DeltaScore_ByCategory (AcademicYearID, OGP_StudentID, DeltaCategoryID, DisplayName, DeltaScore, DeltaValue, DeltaScore_Trend)
	
	SELECT 
	AcademicYearID,
	OGP_StudentID,
	DeltaCategoryID,
	DisplayName,
	Latest_Data_Score,
	Latest_Data_Value,
	Latest_Data_Score - TrendStartScore AS DeltaScore_Trend
	FROM
		(
		SELECT DISTINCT
			OGP_Enrolment.AcademicYearID,
			OGP_Student.ID AS OGP_StudentID,
			Delta_Student_Stat.DeltaCategoryID,
			Delta_Category.DisplayName,
			Delta_Student_Stat.Latest_Data_Score,
			Delta_Student_Stat.Latest_Data_Value,
			CASE (Delta_Student_Stat.Latest_Data_Week - @NumWeeksTrend)
				WHEN 1 THEN Wk1_Score
				WHEN 2 THEN Wk2_Score
				WHEN 3 THEN Wk3_Score
				WHEN 4 THEN Wk4_Score
				WHEN 5 THEN Wk5_Score
				WHEN 6 THEN Wk6_Score
				WHEN 7 THEN Wk7_Score
				WHEN 8 THEN Wk8_Score
				WHEN 9 THEN Wk9_Score
				WHEN 10 THEN Wk10_Score
				WHEN 11 THEN Wk11_Score
				WHEN 12 THEN Wk12_Score
				WHEN 13 THEN Wk13_Score
				WHEN 14 THEN Wk14_Score
				WHEN 15 THEN Wk15_Score
				WHEN 16 THEN Wk16_Score
				WHEN 17 THEN Wk17_Score
				WHEN 18 THEN Wk18_Score
				WHEN 19 THEN Wk19_Score
				WHEN 20 THEN Wk20_Score
				WHEN 21 THEN Wk21_Score
				WHEN 22 THEN Wk22_Score
				WHEN 23 THEN Wk23_Score
				WHEN 24 THEN Wk24_Score
				WHEN 25 THEN Wk25_Score
				WHEN 26 THEN Wk26_Score
				WHEN 27 THEN Wk27_Score
				WHEN 28 THEN Wk28_Score
				WHEN 29 THEN Wk29_Score
				WHEN 30 THEN Wk30_Score
				WHEN 31 THEN Wk31_Score
				WHEN 32 THEN Wk32_Score
				WHEN 33 THEN Wk33_Score
				WHEN 34 THEN Wk34_Score
				WHEN 35 THEN Wk35_Score
				WHEN 36 THEN Wk36_Score
				WHEN 37 THEN Wk37_Score
				WHEN 38 THEN Wk38_Score
				WHEN 39 THEN Wk39_Score
				WHEN 40 THEN Wk40_Score
				WHEN 41 THEN Wk41_Score
				WHEN 42 THEN Wk42_Score
				WHEN 43 THEN Wk43_Score
				WHEN 44 THEN Wk44_Score
				WHEN 45 THEN Wk45_Score
				WHEN 46 THEN Wk46_Score
				WHEN 47 THEN Wk47_Score
				WHEN 48 THEN Wk48_Score
				WHEN 49 THEN Wk49_Score
				WHEN 50 THEN Wk50_Score
				WHEN 51 THEN Wk51_Score
				WHEN 52 THEN Wk52_Score
				WHEN 53 THEN Wk53_Score
			ELSE Wk1_Score END AS TrendStartScore --If the difference ever drops below 1, just use wk1
		FROM
			OGP_Student
				INNER JOIN OGP_Enrolment
					ON OGP_Student.ID = OGP_Enrolment.OGP_StudentID
--					INNER JOIN OGP_GroupEnrolment
--					ON OGP_GroupEnrolment.OGP_EnrolmentID = OGP_Enrolment.ID
				LEFT JOIN Delta_Student_Stat
					ON Delta_Student_Stat.OGP_StudentID = OGP_Student.ID
						LEFT JOIN Delta_Category
							ON Delta_Student_Stat.DeltaCategoryID = Delta_Category.ID AND Delta_Category.IsActive = 1
		WHERE 
			OGP_Student.StudentRef = @ID AND  OGP_Enrolment.AcademicYearID = @AcademicYearID
			AND OGP_Enrolment.RecordStatus <> 'Obsolete' AND OGP_Student.RecordStatus <> 'Obsolete'
		) t

END

IF @GroupingType = 'STUDENTVIEW'
BEGIN

	--Work out (based on the Enrolments included in the selection), the Completion ID for each Student
	INSERT #TempStudentCompletion (AcademicYearID, OGP_StudentID, StudentCompletionID)
	SELECT 
		t.AcademicYearID,
		t.OGP_StudentID,
		(CASE WHEN CountCompletionID_1 > 0 THEN 1 ELSE --cont
			(CASE WHEN CountCompletionID_2 > 0 THEN 2 ELSE --complete
				(CASE WHEN CountCompletionID_3 > 0 THEN 3 ELSE --w/drawn
					(CASE WHEN CountCompletionID_6 > 0 THEN 6 ELSE --temp w/drawn
						(CASE WHEN CountCompletionID_4 > 0 THEN 4 ELSE --Xfer
						1
						END) -- Xfer
					END)
				END)
			END)
		END) AS StudentCompletionID 
	FROM 
	(
		SELECT
			tt.AcademicYearID, tt.OGP_StudentID, SUM(tt.CountCompletionID_1) AS CountCompletionID_1, SUM(tt.CountCompletionID_2) AS CountCompletionID_2,
			SUM(tt.CountCompletionID_3) AS CountCompletionID_3, SUM(tt.CountCompletionID_4) AS CountCompletionID_4, SUM(tt.CountCompletionID_6) AS CountCompletionID_6
		FROM
		(
			SELECT DISTINCT
				OGP_Enrolment.AcademicYearID,
				OGP_Enrolment.OGP_StudentID, 
				(SELECT Count(E.OverallCompletionID) FROM OGP_Enrolment E WHERE E.OverallCompletionID = 1 AND E.ID = OGP_Enrolment.ID) AS CountCompletionID_1,
				(SELECT Count(E.OverallCompletionID) FROM OGP_Enrolment E WHERE E.OverallCompletionID = 2 AND E.ID = OGP_Enrolment.ID) AS CountCompletionID_2,
				(SELECT Count(E.OverallCompletionID) FROM OGP_Enrolment E WHERE E.OverallCompletionID = 3 AND E.ID = OGP_Enrolment.ID) AS CountCompletionID_3,
				(SELECT Count(E.OverallCompletionID) FROM OGP_Enrolment E WHERE E.OverallCompletionID = 4 AND E.ID = OGP_Enrolment.ID) AS CountCompletionID_4,
				(SELECT Count(E.OverallCompletionID) FROM OGP_Enrolment E WHERE E.OverallCompletionID = 6 AND E.ID = OGP_Enrolment.ID) AS CountCompletionID_6
			FROM 
				OGP_Student INNER JOIN OGP_Enrolment ON OGP_Student.ID = OGP_Enrolment.OGP_StudentID
			WHERE 
				OGP_Student.StudentRef = @ID AND OGP_Enrolment.AcademicYearID = @AcademicYearID
				AND OGP_Enrolment.RecordStatus <> 'Obsolete' AND OGP_Student.RecordStatus <> 'Obsolete'
		) tt
		GROUP BY
			tt.AcademicYearID, tt.OGP_StudentID
			
	) t

	INSERT #Temp_DeltaScore_Overall (AcademicYearID, OGP_StudentID, DeltaScore_Overall, DeltaScore_Overall_Trend, Latest_Data_Date, Latest_Data_Week)
	SELECT 
		AcademicYearID,
		OGP_StudentID,
		Latest_Data_StudentScore,
		Latest_Data_StudentScore - TrendStartScore AS DeltaScore_Overall_Trend,
		Latest_Data_Date,
		Latest_Data_Week
		FROM
		(
		SELECT DISTINCT
			OGP_Enrolment.AcademicYearID,
			OGP_Student.ID AS OGP_StudentID,
			Delta_Student_Score.Latest_Data_StudentScore,
			--Delta_Student_Score.Latest_Data_Week - @NumWeeksTrend AS TrendStartWeekNum,
			CASE (Delta_Student_Score.Latest_Data_Week - @NumWeeksTrend)
				WHEN 1 THEN Wk1_StudentScore_Overall
				WHEN 2 THEN Wk2_StudentScore_Overall
				WHEN 3 THEN Wk3_StudentScore_Overall
				WHEN 4 THEN Wk4_StudentScore_Overall
				WHEN 5 THEN Wk5_StudentScore_Overall
				WHEN 6 THEN Wk6_StudentScore_Overall
				WHEN 7 THEN Wk7_StudentScore_Overall
				WHEN 8 THEN Wk8_StudentScore_Overall
				WHEN 9 THEN Wk9_StudentScore_Overall
				WHEN 10 THEN Wk10_StudentScore_Overall
				WHEN 11 THEN Wk11_StudentScore_Overall
				WHEN 12 THEN Wk12_StudentScore_Overall
				WHEN 13 THEN Wk13_StudentScore_Overall
				WHEN 14 THEN Wk14_StudentScore_Overall
				WHEN 15 THEN Wk15_StudentScore_Overall
				WHEN 16 THEN Wk16_StudentScore_Overall
				WHEN 17 THEN Wk17_StudentScore_Overall
				WHEN 18 THEN Wk18_StudentScore_Overall
				WHEN 19 THEN Wk19_StudentScore_Overall
				WHEN 20 THEN Wk20_StudentScore_Overall
				WHEN 21 THEN Wk21_StudentScore_Overall
				WHEN 22 THEN Wk22_StudentScore_Overall
				WHEN 23 THEN Wk23_StudentScore_Overall
				WHEN 24 THEN Wk24_StudentScore_Overall
				WHEN 25 THEN Wk25_StudentScore_Overall
				WHEN 26 THEN Wk26_StudentScore_Overall
				WHEN 27 THEN Wk27_StudentScore_Overall
				WHEN 28 THEN Wk28_StudentScore_Overall
				WHEN 29 THEN Wk29_StudentScore_Overall
				WHEN 30 THEN Wk30_StudentScore_Overall
				WHEN 31 THEN Wk31_StudentScore_Overall
				WHEN 32 THEN Wk32_StudentScore_Overall
				WHEN 33 THEN Wk33_StudentScore_Overall
				WHEN 34 THEN Wk34_StudentScore_Overall
				WHEN 35 THEN Wk35_StudentScore_Overall
				WHEN 36 THEN Wk36_StudentScore_Overall
				WHEN 37 THEN Wk37_StudentScore_Overall
				WHEN 38 THEN Wk38_StudentScore_Overall
				WHEN 39 THEN Wk39_StudentScore_Overall
				WHEN 40 THEN Wk40_StudentScore_Overall
				WHEN 41 THEN Wk41_StudentScore_Overall
				WHEN 42 THEN Wk42_StudentScore_Overall
				WHEN 43 THEN Wk43_StudentScore_Overall
				WHEN 44 THEN Wk44_StudentScore_Overall
				WHEN 45 THEN Wk45_StudentScore_Overall
				WHEN 46 THEN Wk46_StudentScore_Overall
				WHEN 47 THEN Wk47_StudentScore_Overall
				WHEN 48 THEN Wk48_StudentScore_Overall
				WHEN 49 THEN Wk49_StudentScore_Overall
				WHEN 50 THEN Wk50_StudentScore_Overall
				WHEN 51 THEN Wk51_StudentScore_Overall
				WHEN 52 THEN Wk52_StudentScore_Overall
				WHEN 53 THEN Wk53_StudentScore_Overall
			ELSE 
				Wk1_StudentScore_Overall END AS TrendStartScore, --If the difference ever drops below 1, just use wk1
			Delta_Student_Score.Latest_Data_Date,
			Delta_Student_Score.Latest_Data_Week
		FROM
			OGP_Student
				INNER JOIN OGP_Enrolment
					ON OGP_Student.ID = OGP_Enrolment.OGP_StudentID
--					INNER JOIN OGP_GroupEnrolment
--						ON OGP_GroupEnrolment.OGP_EnrolmentID = OGP_Enrolment.ID
				LEFT JOIN Delta_Student_Score
					ON Delta_Student_Score.OGP_StudentID = OGP_Student.ID
		WHERE 
			OGP_Student.StudentRef = @ID AND OGP_Enrolment.AcademicYearID = @AcademicYearID
			AND OGP_Enrolment.RecordStatus <> 'Obsolete' AND OGP_Student.RecordStatus <> 'Obsolete'
		) t

	INSERT #Temp_DeltaScore_ByCategory (AcademicYearID, OGP_StudentID, DeltaCategoryID, DisplayName, DeltaScore, DeltaValue, DeltaScore_Trend)
	
	SELECT 
	AcademicYearID,
	OGP_StudentID,
	DeltaCategoryID,
	DisplayName,
	Latest_Data_Score,
	Latest_Data_Value,
	Latest_Data_Score - TrendStartScore AS DeltaScore_Trend
	FROM
		(
		SELECT DISTINCT
			OGP_Enrolment.AcademicYearID,
			OGP_Student.ID AS OGP_StudentID,
			Delta_Student_Stat.DeltaCategoryID,
			Delta_Category.DisplayName,
			Delta_Student_Stat.Latest_Data_Score,
			Delta_Student_Stat.Latest_Data_Value,
			CASE (Delta_Student_Stat.Latest_Data_Week - @NumWeeksTrend)
				WHEN 1 THEN Wk1_Score
				WHEN 2 THEN Wk2_Score
				WHEN 3 THEN Wk3_Score
				WHEN 4 THEN Wk4_Score
				WHEN 5 THEN Wk5_Score
				WHEN 6 THEN Wk6_Score
				WHEN 7 THEN Wk7_Score
				WHEN 8 THEN Wk8_Score
				WHEN 9 THEN Wk9_Score
				WHEN 10 THEN Wk10_Score
				WHEN 11 THEN Wk11_Score
				WHEN 12 THEN Wk12_Score
				WHEN 13 THEN Wk13_Score
				WHEN 14 THEN Wk14_Score
				WHEN 15 THEN Wk15_Score
				WHEN 16 THEN Wk16_Score
				WHEN 17 THEN Wk17_Score
				WHEN 18 THEN Wk18_Score
				WHEN 19 THEN Wk19_Score
				WHEN 20 THEN Wk20_Score
				WHEN 21 THEN Wk21_Score
				WHEN 22 THEN Wk22_Score
				WHEN 23 THEN Wk23_Score
				WHEN 24 THEN Wk24_Score
				WHEN 25 THEN Wk25_Score
				WHEN 26 THEN Wk26_Score
				WHEN 27 THEN Wk27_Score
				WHEN 28 THEN Wk28_Score
				WHEN 29 THEN Wk29_Score
				WHEN 30 THEN Wk30_Score
				WHEN 31 THEN Wk31_Score
				WHEN 32 THEN Wk32_Score
				WHEN 33 THEN Wk33_Score
				WHEN 34 THEN Wk34_Score
				WHEN 35 THEN Wk35_Score
				WHEN 36 THEN Wk36_Score
				WHEN 37 THEN Wk37_Score
				WHEN 38 THEN Wk38_Score
				WHEN 39 THEN Wk39_Score
				WHEN 40 THEN Wk40_Score
				WHEN 41 THEN Wk41_Score
				WHEN 42 THEN Wk42_Score
				WHEN 43 THEN Wk43_Score
				WHEN 44 THEN Wk44_Score
				WHEN 45 THEN Wk45_Score
				WHEN 46 THEN Wk46_Score
				WHEN 47 THEN Wk47_Score
				WHEN 48 THEN Wk48_Score
				WHEN 49 THEN Wk49_Score
				WHEN 50 THEN Wk50_Score
				WHEN 51 THEN Wk51_Score
				WHEN 52 THEN Wk52_Score
				WHEN 53 THEN Wk53_Score
			ELSE Wk1_Score END AS TrendStartScore --If the difference ever drops below 1, just use wk1
		FROM
			OGP_Student
				INNER JOIN OGP_Enrolment
					ON OGP_Student.ID = OGP_Enrolment.OGP_StudentID
--					INNER JOIN OGP_GroupEnrolment
--					ON OGP_GroupEnrolment.OGP_EnrolmentID = OGP_Enrolment.ID
				LEFT JOIN Delta_Student_Stat
					ON Delta_Student_Stat.OGP_StudentID = OGP_Student.ID
						LEFT JOIN Delta_Category
							ON Delta_Student_Stat.DeltaCategoryID = Delta_Category.ID AND Delta_Category.IsActive = 1 AND Delta_Category.ContributeToStudentScore = 1
		WHERE 
			OGP_Student.StudentRef = @ID AND OGP_Enrolment.AcademicYearID = @AcademicYearID
			AND OGP_Enrolment.RecordStatus <> 'Obsolete' AND OGP_Student.RecordStatus <> 'Obsolete'
		) t

END

IF @GroupingType = 'Course' 
BEGIN

	--Work out (based on the Enrolments included in the selection), the Completion ID for each Student
	INSERT #TempStudentCompletion (AcademicYearID, OGP_StudentID, StudentCompletionID)
	SELECT 
		t.AcademicYearID,
		t.OGP_StudentID,
		(CASE WHEN CountCompletionID_1 > 0 THEN 1 ELSE --cont
			(CASE WHEN CountCompletionID_2 > 0 THEN 2 ELSE --complete
				(CASE WHEN CountCompletionID_3 > 0 THEN 3 ELSE --w/drawn
					(CASE WHEN CountCompletionID_6 > 0 THEN 6 ELSE --temp w/drawn
						(CASE WHEN CountCompletionID_4 > 0 THEN 4 ELSE --Xfer
						1
						END) -- Xfer
					END)
				END)
			END)
		END) AS StudentCompletionID 
	FROM 
	(
		SELECT
			tt.AcademicYearID, tt.OGP_StudentID, SUM(tt.CountCompletionID_1) AS CountCompletionID_1, SUM(tt.CountCompletionID_2) AS CountCompletionID_2,
			SUM(tt.CountCompletionID_3) AS CountCompletionID_3, SUM(tt.CountCompletionID_4) AS CountCompletionID_4, SUM(tt.CountCompletionID_6) AS CountCompletionID_6
		FROM
		(
			SELECT DISTINCT
				OGP_Enrolment.AcademicYearID,
				OGP_Enrolment.OGP_StudentID, 
				(SELECT Count(E.OverallCompletionID) FROM OGP_Enrolment E WHERE E.OverallCompletionID = 1 AND E.ID = OGP_Enrolment.ID) AS CountCompletionID_1,
				(SELECT Count(E.OverallCompletionID) FROM OGP_Enrolment E WHERE E.OverallCompletionID = 2 AND E.ID = OGP_Enrolment.ID) AS CountCompletionID_2,
				(SELECT Count(E.OverallCompletionID) FROM OGP_Enrolment E WHERE E.OverallCompletionID = 3 AND E.ID = OGP_Enrolment.ID) AS CountCompletionID_3,
				(SELECT Count(E.OverallCompletionID) FROM OGP_Enrolment E WHERE E.OverallCompletionID = 4 AND E.ID = OGP_Enrolment.ID) AS CountCompletionID_4,
				(SELECT Count(E.OverallCompletionID) FROM OGP_Enrolment E WHERE E.OverallCompletionID = 6 AND E.ID = OGP_Enrolment.ID) AS CountCompletionID_6
			FROM 
				OGP_Student INNER JOIN OGP_Enrolment ON OGP_Student.ID = OGP_Enrolment.OGP_StudentID
			WHERE 
				OGP_Enrolment.CourseID = CAST(@ID AS INT) AND OGP_Enrolment.AcademicYearID = @AcademicYearID
				AND OGP_Enrolment.RecordStatus <> 'Obsolete' AND OGP_Student.RecordStatus <> 'Obsolete'
		) tt
		GROUP BY
			tt.AcademicYearID, tt.OGP_StudentID
	) t

	INSERT #Temp_DeltaScore_Overall (AcademicYearID, OGP_StudentID, DeltaScore_Overall, DeltaScore_Overall_Trend, Latest_Data_Date, Latest_Data_Week)
	SELECT 
		AcademicYearID,
		OGP_StudentID,
		Latest_Data_Score,
		Latest_Data_Score - TrendStartScore AS DeltaScore_Overall_Trend,
		Latest_Data_Date,
		Latest_Data_Week
		FROM
		(
		SELECT DISTINCT
			OGP_Enrolment.AcademicYearID,
			OGP_Student.ID AS OGP_StudentID,
			Delta_Student_Score.Latest_Data_Score,
			CASE (Delta_Student_Score.Latest_Data_Week - @NumWeeksTrend)
				WHEN 1 THEN Wk1_Score_Overall
				WHEN 2 THEN Wk2_Score_Overall
				WHEN 3 THEN Wk3_Score_Overall
				WHEN 4 THEN Wk4_Score_Overall
				WHEN 5 THEN Wk5_Score_Overall
				WHEN 6 THEN Wk6_Score_Overall
				WHEN 7 THEN Wk7_Score_Overall
				WHEN 8 THEN Wk8_Score_Overall
				WHEN 9 THEN Wk9_Score_Overall
				WHEN 10 THEN Wk10_Score_Overall
				WHEN 11 THEN Wk11_Score_Overall
				WHEN 12 THEN Wk12_Score_Overall
				WHEN 13 THEN Wk13_Score_Overall
				WHEN 14 THEN Wk14_Score_Overall
				WHEN 15 THEN Wk15_Score_Overall
				WHEN 16 THEN Wk16_Score_Overall
				WHEN 17 THEN Wk17_Score_Overall
				WHEN 18 THEN Wk18_Score_Overall
				WHEN 19 THEN Wk19_Score_Overall
				WHEN 20 THEN Wk20_Score_Overall
				WHEN 21 THEN Wk21_Score_Overall
				WHEN 22 THEN Wk22_Score_Overall
				WHEN 23 THEN Wk23_Score_Overall
				WHEN 24 THEN Wk24_Score_Overall
				WHEN 25 THEN Wk25_Score_Overall
				WHEN 26 THEN Wk26_Score_Overall
				WHEN 27 THEN Wk27_Score_Overall
				WHEN 28 THEN Wk28_Score_Overall
				WHEN 29 THEN Wk29_Score_Overall
				WHEN 30 THEN Wk30_Score_Overall
				WHEN 31 THEN Wk31_Score_Overall
				WHEN 32 THEN Wk32_Score_Overall
				WHEN 33 THEN Wk33_Score_Overall
				WHEN 34 THEN Wk34_Score_Overall
				WHEN 35 THEN Wk35_Score_Overall
				WHEN 36 THEN Wk36_Score_Overall
				WHEN 37 THEN Wk37_Score_Overall
				WHEN 38 THEN Wk38_Score_Overall
				WHEN 39 THEN Wk39_Score_Overall
				WHEN 40 THEN Wk40_Score_Overall
				WHEN 41 THEN Wk41_Score_Overall
				WHEN 42 THEN Wk42_Score_Overall
				WHEN 43 THEN Wk43_Score_Overall
				WHEN 44 THEN Wk44_Score_Overall
				WHEN 45 THEN Wk45_Score_Overall
				WHEN 46 THEN Wk46_Score_Overall
				WHEN 47 THEN Wk47_Score_Overall
				WHEN 48 THEN Wk48_Score_Overall
				WHEN 49 THEN Wk49_Score_Overall
				WHEN 50 THEN Wk50_Score_Overall
				WHEN 51 THEN Wk51_Score_Overall
				WHEN 52 THEN Wk52_Score_Overall
				WHEN 53 THEN Wk53_Score_Overall
			ELSE Wk1_Score_Overall END AS TrendStartScore, --If the difference ever drops below 1, just use wk1
			Delta_Student_Score.Latest_Data_Date,
			Delta_Student_Score.Latest_Data_Week
		FROM
			OGP_Student
				INNER JOIN OGP_Enrolment
				ON OGP_Student.ID = OGP_Enrolment.OGP_StudentID
					LEFT JOIN Delta_Student_Score
					ON Delta_Student_Score.OGP_StudentID = OGP_Student.ID
		WHERE 
			OGP_Enrolment.CourseID = CAST(@ID AS INT) AND OGP_Enrolment.AcademicYearID = @AcademicYearID
			AND OGP_Enrolment.RecordStatus <> 'Obsolete' AND OGP_Student.RecordStatus <> 'Obsolete'
		) t

	INSERT #Temp_DeltaScore_ByCategory (AcademicYearID, OGP_StudentID, DeltaCategoryID, DisplayName, DeltaScore, DeltaValue, DeltaScore_Trend)
	SELECT 
	AcademicYearID,
	OGP_StudentID,
	DeltaCategoryID,
	DisplayName,
	Latest_Data_Score,
	Latest_Data_Value,
	Latest_Data_Score - TrendStartScore AS DeltaScore_Trend
	FROM
		(
		SELECT DISTINCT
			OGP_Enrolment.AcademicYearID,
			OGP_Student.ID AS OGP_StudentID,
			Delta_Student_Stat.DeltaCategoryID,
			Delta_Category.DisplayName,
			Delta_Student_Stat.Latest_Data_Score,
			Delta_Student_Stat.Latest_Data_Value,
			CASE (Delta_Student_Stat.Latest_Data_Week - @NumWeeksTrend)
				WHEN 1 THEN Wk1_Score
				WHEN 2 THEN Wk2_Score
				WHEN 3 THEN Wk3_Score
				WHEN 4 THEN Wk4_Score
				WHEN 5 THEN Wk5_Score
				WHEN 6 THEN Wk6_Score
				WHEN 7 THEN Wk7_Score
				WHEN 8 THEN Wk8_Score
				WHEN 9 THEN Wk9_Score
				WHEN 10 THEN Wk10_Score
				WHEN 11 THEN Wk11_Score
				WHEN 12 THEN Wk12_Score
				WHEN 13 THEN Wk13_Score
				WHEN 14 THEN Wk14_Score
				WHEN 15 THEN Wk15_Score
				WHEN 16 THEN Wk16_Score
				WHEN 17 THEN Wk17_Score
				WHEN 18 THEN Wk18_Score
				WHEN 19 THEN Wk19_Score
				WHEN 20 THEN Wk20_Score
				WHEN 21 THEN Wk21_Score
				WHEN 22 THEN Wk22_Score
				WHEN 23 THEN Wk23_Score
				WHEN 24 THEN Wk24_Score
				WHEN 25 THEN Wk25_Score
				WHEN 26 THEN Wk26_Score
				WHEN 27 THEN Wk27_Score
				WHEN 28 THEN Wk28_Score
				WHEN 29 THEN Wk29_Score
				WHEN 30 THEN Wk30_Score
				WHEN 31 THEN Wk31_Score
				WHEN 32 THEN Wk32_Score
				WHEN 33 THEN Wk33_Score
				WHEN 34 THEN Wk34_Score
				WHEN 35 THEN Wk35_Score
				WHEN 36 THEN Wk36_Score
				WHEN 37 THEN Wk37_Score
				WHEN 38 THEN Wk38_Score
				WHEN 39 THEN Wk39_Score
				WHEN 40 THEN Wk40_Score
				WHEN 41 THEN Wk41_Score
				WHEN 42 THEN Wk42_Score
				WHEN 43 THEN Wk43_Score
				WHEN 44 THEN Wk44_Score
				WHEN 45 THEN Wk45_Score
				WHEN 46 THEN Wk46_Score
				WHEN 47 THEN Wk47_Score
				WHEN 48 THEN Wk48_Score
				WHEN 49 THEN Wk49_Score
				WHEN 50 THEN Wk50_Score
				WHEN 51 THEN Wk51_Score
				WHEN 52 THEN Wk52_Score
				WHEN 53 THEN Wk53_Score
			ELSE Wk1_Score END AS TrendStartScore --If the difference ever drops below 1, just use wk1
		FROM
			OGP_Student
				INNER JOIN OGP_Enrolment
				ON OGP_Student.ID = OGP_Enrolment.OGP_StudentID
					LEFT JOIN Delta_Student_Stat
					ON Delta_Student_Stat.OGP_StudentID = OGP_Student.ID
						LEFT JOIN Delta_Category
						ON Delta_Student_Stat.DeltaCategoryID = Delta_Category.ID
						AND Delta_Category.IsActive = 1
		WHERE 
			OGP_Enrolment.CourseID = CAST(@ID AS INT) AND OGP_Enrolment.AcademicYearID = @AcademicYearID
			AND OGP_Enrolment.RecordStatus <> 'Obsolete' AND OGP_Student.RecordStatus <> 'Obsolete'
		) t


END



IF @GroupingType = 'LearningAim'
BEGIN

	--Work out (based on the Enrolments included in the selection), the Completion ID for each Student
	INSERT #TempStudentCompletion (AcademicYearID, OGP_StudentID, StudentCompletionID)
	SELECT 
		t.AcademicYearID,
		t.OGP_StudentID,
		(CASE WHEN CountCompletionID_1 > 0 THEN 1 ELSE --cont
			(CASE WHEN CountCompletionID_2 > 0 THEN 2 ELSE --complete
				(CASE WHEN CountCompletionID_3 > 0 THEN 3 ELSE --w/drawn
					(CASE WHEN CountCompletionID_6 > 0 THEN 6 ELSE --temp w/drawn
						(CASE WHEN CountCompletionID_4 > 0 THEN 4 ELSE --Xfer
						1
						END) -- Xfer
					END)
				END)
			END)
		END) AS StudentCompletionID 
	FROM 
	(
		SELECT
			tt.AcademicYearID, tt.OGP_StudentID, SUM(tt.CountCompletionID_1) AS CountCompletionID_1, SUM(tt.CountCompletionID_2) AS CountCompletionID_2,
			SUM(tt.CountCompletionID_3) AS CountCompletionID_3, SUM(tt.CountCompletionID_4) AS CountCompletionID_4, SUM(tt.CountCompletionID_6) AS CountCompletionID_6
		FROM
		(
			SELECT DISTINCT
				OGP_Enrolment.AcademicYearID,
				OGP_Enrolment.OGP_StudentID, 
				(SELECT Count(E.OverallCompletionID) FROM OGP_Enrolment E WHERE E.OverallCompletionID = 1 AND E.ID = OGP_Enrolment.ID) AS CountCompletionID_1,
				(SELECT Count(E.OverallCompletionID) FROM OGP_Enrolment E WHERE E.OverallCompletionID = 2 AND E.ID = OGP_Enrolment.ID) AS CountCompletionID_2,
				(SELECT Count(E.OverallCompletionID) FROM OGP_Enrolment E WHERE E.OverallCompletionID = 3 AND E.ID = OGP_Enrolment.ID) AS CountCompletionID_3,
				(SELECT Count(E.OverallCompletionID) FROM OGP_Enrolment E WHERE E.OverallCompletionID = 4 AND E.ID = OGP_Enrolment.ID) AS CountCompletionID_4,
				(SELECT Count(E.OverallCompletionID) FROM OGP_Enrolment E WHERE E.OverallCompletionID = 6 AND E.ID = OGP_Enrolment.ID) AS CountCompletionID_6
			FROM 
				OGP_Student
					INNER JOIN OGP_Enrolment
					ON OGP_Student.ID = OGP_Enrolment.OGP_StudentID
						INNER JOIN OGP_EnrolmentStatus
						ON OGP_Enrolment.ID = OGP_EnrolmentStatus.OGP_EnrolmentID
			WHERE OGP_EnrolmentStatus.LearningAimRef = @ID AND OGP_Enrolment.AcademicYearID = @AcademicYearID
			AND OGP_Enrolment.RecordStatus <> 'Obsolete' AND OGP_Student.RecordStatus <> 'Obsolete' AND OGP_EnrolmentStatus.RecordStatus <> 'Obsolete'
		) tt
		GROUP BY
			tt.AcademicYearID, tt.OGP_StudentID		
	) t


	INSERT #Temp_DeltaScore_Overall (AcademicYearID, OGP_StudentID, DeltaScore_Overall, DeltaScore_Overall_Trend, Latest_Data_Date, Latest_Data_Week)
	SELECT 
		AcademicYearID,
		OGP_StudentID,
		Latest_Data_Score,
		Latest_Data_Score - TrendStartScore AS DeltaScore_Overall_Trend,
		Latest_Data_Date,
		Latest_Data_Week
		FROM
		(
		SELECT DISTINCT
			OGP_Enrolment.AcademicYearID,
			OGP_Student.ID AS OGP_StudentID,
			Delta_Student_Score.Latest_Data_Score,
			CASE (Delta_Student_Score.Latest_Data_Week - @NumWeeksTrend)
				WHEN 1 THEN Wk1_Score_Overall
				WHEN 2 THEN Wk2_Score_Overall
				WHEN 3 THEN Wk3_Score_Overall
				WHEN 4 THEN Wk4_Score_Overall
				WHEN 5 THEN Wk5_Score_Overall
				WHEN 6 THEN Wk6_Score_Overall
				WHEN 7 THEN Wk7_Score_Overall
				WHEN 8 THEN Wk8_Score_Overall
				WHEN 9 THEN Wk9_Score_Overall
				WHEN 10 THEN Wk10_Score_Overall
				WHEN 11 THEN Wk11_Score_Overall
				WHEN 12 THEN Wk12_Score_Overall
				WHEN 13 THEN Wk13_Score_Overall
				WHEN 14 THEN Wk14_Score_Overall
				WHEN 15 THEN Wk15_Score_Overall
				WHEN 16 THEN Wk16_Score_Overall
				WHEN 17 THEN Wk17_Score_Overall
				WHEN 18 THEN Wk18_Score_Overall
				WHEN 19 THEN Wk19_Score_Overall
				WHEN 20 THEN Wk20_Score_Overall
				WHEN 21 THEN Wk21_Score_Overall
				WHEN 22 THEN Wk22_Score_Overall
				WHEN 23 THEN Wk23_Score_Overall
				WHEN 24 THEN Wk24_Score_Overall
				WHEN 25 THEN Wk25_Score_Overall
				WHEN 26 THEN Wk26_Score_Overall
				WHEN 27 THEN Wk27_Score_Overall
				WHEN 28 THEN Wk28_Score_Overall
				WHEN 29 THEN Wk29_Score_Overall
				WHEN 30 THEN Wk30_Score_Overall
				WHEN 31 THEN Wk31_Score_Overall
				WHEN 32 THEN Wk32_Score_Overall
				WHEN 33 THEN Wk33_Score_Overall
				WHEN 34 THEN Wk34_Score_Overall
				WHEN 35 THEN Wk35_Score_Overall
				WHEN 36 THEN Wk36_Score_Overall
				WHEN 37 THEN Wk37_Score_Overall
				WHEN 38 THEN Wk38_Score_Overall
				WHEN 39 THEN Wk39_Score_Overall
				WHEN 40 THEN Wk40_Score_Overall
				WHEN 41 THEN Wk41_Score_Overall
				WHEN 42 THEN Wk42_Score_Overall
				WHEN 43 THEN Wk43_Score_Overall
				WHEN 44 THEN Wk44_Score_Overall
				WHEN 45 THEN Wk45_Score_Overall
				WHEN 46 THEN Wk46_Score_Overall
				WHEN 47 THEN Wk47_Score_Overall
				WHEN 48 THEN Wk48_Score_Overall
				WHEN 49 THEN Wk49_Score_Overall
				WHEN 50 THEN Wk50_Score_Overall
				WHEN 51 THEN Wk51_Score_Overall
				WHEN 52 THEN Wk52_Score_Overall
				WHEN 53 THEN Wk53_Score_Overall
			ELSE Wk1_Score_Overall END AS TrendStartScore, --If the difference ever drops below 1, just use wk1
			Delta_Student_Score.Latest_Data_Date,
			Delta_Student_Score.Latest_Data_Week
		FROM
			OGP_Student
				INNER JOIN OGP_Enrolment
				ON OGP_Student.ID = OGP_Enrolment.OGP_StudentID
					INNER JOIN OGP_EnrolmentStatus
					ON OGP_Enrolment.ID = OGP_EnrolmentStatus.OGP_EnrolmentID
						LEFT JOIN Delta_Student_Score
						ON Delta_Student_Score.OGP_StudentID = OGP_Student.ID
		WHERE 
			OGP_EnrolmentStatus.LearningAimRef = @ID AND OGP_Enrolment.AcademicYearID = @AcademicYearID
			AND OGP_Enrolment.RecordStatus <> 'Obsolete' AND OGP_Student.RecordStatus <> 'Obsolete' AND OGP_EnrolmentStatus.RecordStatus <> 'Obsolete'
		) t
		
	INSERT #Temp_DeltaScore_ByCategory (AcademicYearID, OGP_StudentID, DeltaCategoryID, DisplayName, DeltaScore, DeltaValue, DeltaScore_Trend)
	SELECT 
	AcademicYearID,
	OGP_StudentID,
	DeltaCategoryID,
	DisplayName,
	Latest_Data_Score,
	Latest_Data_Value,
	Latest_Data_Score - TrendStartScore AS DeltaScore_Trend
	FROM
		(
		SELECT DISTINCT
			OGP_Enrolment.AcademicYearID,
			OGP_Student.ID AS OGP_StudentID,
			Delta_Student_Stat.DeltaCategoryID,
			Delta_Category.DisplayName,
			Delta_Student_Stat.Latest_Data_Score,
			Delta_Student_Stat.Latest_Data_Value,
			CASE (Delta_Student_Stat.Latest_Data_Week - @NumWeeksTrend)
				WHEN 1 THEN Wk1_Score
				WHEN 2 THEN Wk2_Score
				WHEN 3 THEN Wk3_Score
				WHEN 4 THEN Wk4_Score
				WHEN 5 THEN Wk5_Score
				WHEN 6 THEN Wk6_Score
				WHEN 7 THEN Wk7_Score
				WHEN 8 THEN Wk8_Score
				WHEN 9 THEN Wk9_Score
				WHEN 10 THEN Wk10_Score
				WHEN 11 THEN Wk11_Score
				WHEN 12 THEN Wk12_Score
				WHEN 13 THEN Wk13_Score
				WHEN 14 THEN Wk14_Score
				WHEN 15 THEN Wk15_Score
				WHEN 16 THEN Wk16_Score
				WHEN 17 THEN Wk17_Score
				WHEN 18 THEN Wk18_Score
				WHEN 19 THEN Wk19_Score
				WHEN 20 THEN Wk20_Score
				WHEN 21 THEN Wk21_Score
				WHEN 22 THEN Wk22_Score
				WHEN 23 THEN Wk23_Score
				WHEN 24 THEN Wk24_Score
				WHEN 25 THEN Wk25_Score
				WHEN 26 THEN Wk26_Score
				WHEN 27 THEN Wk27_Score
				WHEN 28 THEN Wk28_Score
				WHEN 29 THEN Wk29_Score
				WHEN 30 THEN Wk30_Score
				WHEN 31 THEN Wk31_Score
				WHEN 32 THEN Wk32_Score
				WHEN 33 THEN Wk33_Score
				WHEN 34 THEN Wk34_Score
				WHEN 35 THEN Wk35_Score
				WHEN 36 THEN Wk36_Score
				WHEN 37 THEN Wk37_Score
				WHEN 38 THEN Wk38_Score
				WHEN 39 THEN Wk39_Score
				WHEN 40 THEN Wk40_Score
				WHEN 41 THEN Wk41_Score
				WHEN 42 THEN Wk42_Score
				WHEN 43 THEN Wk43_Score
				WHEN 44 THEN Wk44_Score
				WHEN 45 THEN Wk45_Score
				WHEN 46 THEN Wk46_Score
				WHEN 47 THEN Wk47_Score
				WHEN 48 THEN Wk48_Score
				WHEN 49 THEN Wk49_Score
				WHEN 50 THEN Wk50_Score
				WHEN 51 THEN Wk51_Score
				WHEN 52 THEN Wk52_Score
				WHEN 53 THEN Wk53_Score
			ELSE Wk1_Score END AS TrendStartScore --If the difference ever drops below 1, just use wk1
		FROM
			OGP_Student
				INNER JOIN OGP_Enrolment
				ON OGP_Student.ID = OGP_Enrolment.OGP_StudentID
					INNER JOIN OGP_EnrolmentStatus
					ON OGP_Enrolment.ID = OGP_EnrolmentStatus.OGP_EnrolmentID
						LEFT JOIN Delta_Student_Stat
						ON Delta_Student_Stat.OGP_StudentID = OGP_Student.ID
							LEFT JOIN Delta_Category
							ON Delta_Student_Stat.DeltaCategoryID = Delta_Category.ID
							AND Delta_Category.IsActive = 1
		WHERE 
			OGP_EnrolmentStatus.LearningAimRef = @ID AND OGP_Enrolment.AcademicYearID = @AcademicYearID
			AND OGP_Enrolment.RecordStatus <> 'Obsolete' AND OGP_Student.RecordStatus <> 'Obsolete' AND OGP_EnrolmentStatus.RecordStatus <> 'Obsolete'
		) t
END


IF @GroupingType = 'CollegeStructure'
BEGIN

	--Work out (based on the Enrolments included in the selection), the Completion ID for each Student
	INSERT #TempStudentCompletion (AcademicYearID, OGP_StudentID, StudentCompletionID)
	SELECT 
		t.AcademicYearID,
		t.OGP_StudentID,
		(CASE WHEN CountCompletionID_1 > 0 THEN 1 ELSE --cont
			(CASE WHEN CountCompletionID_2 > 0 THEN 2 ELSE --complete
				(CASE WHEN CountCompletionID_3 > 0 THEN 3 ELSE --w/drawn
					(CASE WHEN CountCompletionID_6 > 0 THEN 6 ELSE --temp w/drawn
						(CASE WHEN CountCompletionID_4 > 0 THEN 4 ELSE --Xfer
						1
						END) -- Xfer
					END)
				END)
			END)
		END) AS StudentCompletionID 
	FROM 
	(
		SELECT
			tt.AcademicYearID, tt.OGP_StudentID, SUM(tt.CountCompletionID_1) AS CountCompletionID_1, SUM(tt.CountCompletionID_2) AS CountCompletionID_2,
			SUM(tt.CountCompletionID_3) AS CountCompletionID_3, SUM(tt.CountCompletionID_4) AS CountCompletionID_4, SUM(tt.CountCompletionID_6) AS CountCompletionID_6
		FROM
		(
			SELECT DISTINCT
				OGP_Enrolment.AcademicYearID,
				OGP_Enrolment.OGP_StudentID, 
				(SELECT Count(E.OverallCompletionID) FROM OGP_Enrolment E WHERE E.OverallCompletionID = 1 AND E.ID = OGP_Enrolment.ID) AS CountCompletionID_1,
				(SELECT Count(E.OverallCompletionID) FROM OGP_Enrolment E WHERE E.OverallCompletionID = 2 AND E.ID = OGP_Enrolment.ID) AS CountCompletionID_2,
				(SELECT Count(E.OverallCompletionID) FROM OGP_Enrolment E WHERE E.OverallCompletionID = 3 AND E.ID = OGP_Enrolment.ID) AS CountCompletionID_3,
				(SELECT Count(E.OverallCompletionID) FROM OGP_Enrolment E WHERE E.OverallCompletionID = 4 AND E.ID = OGP_Enrolment.ID) AS CountCompletionID_4,
				(SELECT Count(E.OverallCompletionID) FROM OGP_Enrolment E WHERE E.OverallCompletionID = 6 AND E.ID = OGP_Enrolment.ID) AS CountCompletionID_6
			FROM
				OGP_Student
					INNER JOIN OGP_Enrolment
					ON OGP_Student.ID = OGP_Enrolment.OGP_StudentID
						INNER JOIN OGP_GroupEnrolment
						ON OGP_GroupEnrolment.OGP_EnrolmentID = OGP_Enrolment.ID
							INNER JOIN OGP_Group
							ON OGP_GroupEnrolment.OGP_GroupID = OGP_Group.ID
								INNER JOIN  OGP_CollegeStructureGroup
								ON OGP_Group.ID = OGP_CollegeStructureGroup.OGP_GroupID					
			WHERE 
				OGP_CollegeStructureGroup.OGP_CollegeStructureID = CAST(@ID AS INT) AND OGP_Enrolment.AcademicYearID = @AcademicYearID
				AND OGP_Enrolment.RecordStatus <> 'Obsolete' AND OGP_Student.RecordStatus <> 'Obsolete' AND OGP_GroupEnrolment.RecordStatus <> 'Obsolete'
				AND OGP_Group.RecordStatus <> 'Obsolete' AND OGP_CollegeStructureGroup.RecordStatus <> 'Obsolete'
		) tt
		GROUP BY
			tt.AcademicYearID, tt.OGP_StudentID				
	) t

	INSERT #Temp_DeltaScore_Overall (AcademicYearID, OGP_StudentID, DeltaScore_Overall, DeltaScore_Overall_Trend, Latest_Data_Date, Latest_Data_Week)
	SELECT 
		AcademicYearID,
		OGP_StudentID,
		Latest_Data_Score,
		Latest_Data_Score - TrendStartScore AS DeltaScore_Overall_Trend,
		Latest_Data_Date,
		Latest_Data_Week
		FROM
		(
		SELECT DISTINCT
			OGP_Enrolment.AcademicYearID,
			OGP_Student.ID AS OGP_StudentID,
			Delta_Student_Score.Latest_Data_Score,
			CASE (Delta_Student_Score.Latest_Data_Week - @NumWeeksTrend)
				WHEN 1 THEN Wk1_Score_Overall
				WHEN 2 THEN Wk2_Score_Overall
				WHEN 3 THEN Wk3_Score_Overall
				WHEN 4 THEN Wk4_Score_Overall
				WHEN 5 THEN Wk5_Score_Overall
				WHEN 6 THEN Wk6_Score_Overall
				WHEN 7 THEN Wk7_Score_Overall
				WHEN 8 THEN Wk8_Score_Overall
				WHEN 9 THEN Wk9_Score_Overall
				WHEN 10 THEN Wk10_Score_Overall
				WHEN 11 THEN Wk11_Score_Overall
				WHEN 12 THEN Wk12_Score_Overall
				WHEN 13 THEN Wk13_Score_Overall
				WHEN 14 THEN Wk14_Score_Overall
				WHEN 15 THEN Wk15_Score_Overall
				WHEN 16 THEN Wk16_Score_Overall
				WHEN 17 THEN Wk17_Score_Overall
				WHEN 18 THEN Wk18_Score_Overall
				WHEN 19 THEN Wk19_Score_Overall
				WHEN 20 THEN Wk20_Score_Overall
				WHEN 21 THEN Wk21_Score_Overall
				WHEN 22 THEN Wk22_Score_Overall
				WHEN 23 THEN Wk23_Score_Overall
				WHEN 24 THEN Wk24_Score_Overall
				WHEN 25 THEN Wk25_Score_Overall
				WHEN 26 THEN Wk26_Score_Overall
				WHEN 27 THEN Wk27_Score_Overall
				WHEN 28 THEN Wk28_Score_Overall
				WHEN 29 THEN Wk29_Score_Overall
				WHEN 30 THEN Wk30_Score_Overall
				WHEN 31 THEN Wk31_Score_Overall
				WHEN 32 THEN Wk32_Score_Overall
				WHEN 33 THEN Wk33_Score_Overall
				WHEN 34 THEN Wk34_Score_Overall
				WHEN 35 THEN Wk35_Score_Overall
				WHEN 36 THEN Wk36_Score_Overall
				WHEN 37 THEN Wk37_Score_Overall
				WHEN 38 THEN Wk38_Score_Overall
				WHEN 39 THEN Wk39_Score_Overall
				WHEN 40 THEN Wk40_Score_Overall
				WHEN 41 THEN Wk41_Score_Overall
				WHEN 42 THEN Wk42_Score_Overall
				WHEN 43 THEN Wk43_Score_Overall
				WHEN 44 THEN Wk44_Score_Overall
				WHEN 45 THEN Wk45_Score_Overall
				WHEN 46 THEN Wk46_Score_Overall
				WHEN 47 THEN Wk47_Score_Overall
				WHEN 48 THEN Wk48_Score_Overall
				WHEN 49 THEN Wk49_Score_Overall
				WHEN 50 THEN Wk50_Score_Overall
				WHEN 51 THEN Wk51_Score_Overall
				WHEN 52 THEN Wk52_Score_Overall
				WHEN 53 THEN Wk53_Score_Overall
			ELSE Wk1_Score_Overall END AS TrendStartScore, --If the difference ever drops below 1, just use wk1
			Delta_Student_Score.Latest_Data_Date,
			Delta_Student_Score.Latest_Data_Week
		FROM
			OGP_Student
				INNER JOIN OGP_Enrolment
				ON OGP_Student.ID = OGP_Enrolment.OGP_StudentID
					INNER JOIN OGP_GroupEnrolment
					ON OGP_GroupEnrolment.OGP_EnrolmentID = OGP_Enrolment.ID
						INNER JOIN OGP_Group
						ON OGP_GroupEnrolment.OGP_GroupID = OGP_Group.ID
							INNER JOIN  OGP_CollegeStructureGroup
							ON OGP_Group.ID = OGP_CollegeStructureGroup.OGP_GroupID
									LEFT JOIN Delta_Student_Score
									ON Delta_Student_Score.OGP_StudentID = OGP_Student.ID

		WHERE 
			OGP_CollegeStructureGroup.OGP_CollegeStructureID = CAST(@ID AS INT) AND OGP_Enrolment.AcademicYearID = @AcademicYearID
			AND OGP_Enrolment.RecordStatus <> 'Obsolete' AND OGP_Student.RecordStatus <> 'Obsolete' AND OGP_GroupEnrolment.RecordStatus <> 'Obsolete'
			AND OGP_Group.RecordStatus <> 'Obsolete' AND OGP_CollegeStructureGroup.RecordStatus <> 'Obsolete'
		) t

	INSERT #Temp_DeltaScore_ByCategory (AcademicYearID, OGP_StudentID, DeltaCategoryID, DisplayName, DeltaScore, DeltaValue, DeltaScore_Trend)
	SELECT 
		AcademicYearID,
		OGP_StudentID,
		DeltaCategoryID,
		DisplayName,
		Latest_Data_Score,
		Latest_Data_Value,
		Latest_Data_Score - TrendStartScore AS DeltaScore_Trend
	FROM
		(
		SELECT DISTINCT
			OGP_Enrolment.AcademicYearID,
			OGP_Student.ID AS OGP_StudentID,
			Delta_Student_Stat.DeltaCategoryID,
			Delta_Category.DisplayName,
			Delta_Student_Stat.Latest_Data_Score,
			Delta_Student_Stat.Latest_Data_Value,
			CASE (Delta_Student_Stat.Latest_Data_Week - @NumWeeksTrend)
				WHEN 1 THEN Wk1_Score
				WHEN 2 THEN Wk2_Score
				WHEN 3 THEN Wk3_Score
				WHEN 4 THEN Wk4_Score
				WHEN 5 THEN Wk5_Score
				WHEN 6 THEN Wk6_Score
				WHEN 7 THEN Wk7_Score
				WHEN 8 THEN Wk8_Score
				WHEN 9 THEN Wk9_Score
				WHEN 10 THEN Wk10_Score
				WHEN 11 THEN Wk11_Score
				WHEN 12 THEN Wk12_Score
				WHEN 13 THEN Wk13_Score
				WHEN 14 THEN Wk14_Score
				WHEN 15 THEN Wk15_Score
				WHEN 16 THEN Wk16_Score
				WHEN 17 THEN Wk17_Score
				WHEN 18 THEN Wk18_Score
				WHEN 19 THEN Wk19_Score
				WHEN 20 THEN Wk20_Score
				WHEN 21 THEN Wk21_Score
				WHEN 22 THEN Wk22_Score
				WHEN 23 THEN Wk23_Score
				WHEN 24 THEN Wk24_Score
				WHEN 25 THEN Wk25_Score
				WHEN 26 THEN Wk26_Score
				WHEN 27 THEN Wk27_Score
				WHEN 28 THEN Wk28_Score
				WHEN 29 THEN Wk29_Score
				WHEN 30 THEN Wk30_Score
				WHEN 31 THEN Wk31_Score
				WHEN 32 THEN Wk32_Score
				WHEN 33 THEN Wk33_Score
				WHEN 34 THEN Wk34_Score
				WHEN 35 THEN Wk35_Score
				WHEN 36 THEN Wk36_Score
				WHEN 37 THEN Wk37_Score
				WHEN 38 THEN Wk38_Score
				WHEN 39 THEN Wk39_Score
				WHEN 40 THEN Wk40_Score
				WHEN 41 THEN Wk41_Score
				WHEN 42 THEN Wk42_Score
				WHEN 43 THEN Wk43_Score
				WHEN 44 THEN Wk44_Score
				WHEN 45 THEN Wk45_Score
				WHEN 46 THEN Wk46_Score
				WHEN 47 THEN Wk47_Score
				WHEN 48 THEN Wk48_Score
				WHEN 49 THEN Wk49_Score
				WHEN 50 THEN Wk50_Score
				WHEN 51 THEN Wk51_Score
				WHEN 52 THEN Wk52_Score
				WHEN 53 THEN Wk53_Score
			ELSE Wk1_Score END AS TrendStartScore --If the difference ever drops below 1, just use wk1
		FROM
			OGP_Student
				INNER JOIN OGP_Enrolment
				ON OGP_Student.ID = OGP_Enrolment.OGP_StudentID
					INNER JOIN OGP_GroupEnrolment
					ON OGP_GroupEnrolment.OGP_EnrolmentID = OGP_Enrolment.ID
						INNER JOIN OGP_Group
						ON OGP_GroupEnrolment.OGP_GroupID = OGP_Group.ID
							INNER JOIN  OGP_CollegeStructureGroup
							ON OGP_Group.ID = OGP_CollegeStructureGroup.OGP_GroupID
								LEFT JOIN Delta_Student_Stat
								ON Delta_Student_Stat.OGP_StudentID = OGP_Student.ID
									LEFT JOIN Delta_Category
									ON Delta_Student_Stat.DeltaCategoryID = Delta_Category.ID
									AND Delta_Category.IsActive = 1
			WHERE 
			OGP_CollegeStructureGroup.OGP_CollegeStructureID = CAST(@ID AS INT) AND OGP_Enrolment.AcademicYearID = @AcademicYearID
			AND OGP_Enrolment.RecordStatus <> 'Obsolete' AND OGP_Student.RecordStatus <> 'Obsolete' AND OGP_GroupEnrolment.RecordStatus <> 'Obsolete'
			AND OGP_Group.RecordStatus <> 'Obsolete' AND OGP_CollegeStructureGroup.RecordStatus <> 'Obsolete'
		) t
END




SELECT 
	OGP_StudentID,
	[Attendance] AS [Attendance_Value], 
	[Punctuality] AS [Punctuality_Value], 
	[Progress] AS [Progress_Value],
	[WorkOutstanding] AS [WorkOutstanding_Value],
	[LateStart] AS [LateStart_Value],
	[ExceptionalEntry] AS [ExceptionalEntry_Value],
	[StudentSurvey] AS [StudentSurvey_Value],
	[WorkPlacement] AS [WorkPlacement_Value],
	[Enrichment] AS [Enrichment_Value], 
	[Comments] AS [Comments_Value],
	[Behavioural] AS [Behavioural_Value],
	[EmployabilitySkills] AS [EmployabilitySkills_Value],
	[User1] AS [User1_Value],
	[User2] AS [User2_Value],
	[User3] AS [User3_Value],
	[User4] AS [User4_Value],
	[User5] AS [User5_Value],
	[User6] AS [User6_Value],
	[User7] AS [User7_Value],
	[User8] AS [User8_Value]

INTO #TempPivot_Value
FROM  
	(
	  SELECT 
		  Delta_Category.SystemName AS DeltaSystemName, 
		  #Temp_DeltaScore_ByCategory.AcademicYearID,
		  #Temp_DeltaScore_ByCategory.[OGP_StudentID], 
		  #Temp_DeltaScore_ByCategory.DeltaValue   
	  FROM 
		#Temp_DeltaScore_ByCategory
		  LEFT JOIN Delta_Category
		  ON Delta_Category.ID = #Temp_DeltaScore_ByCategory.DeltaCategoryID
	) AS SourceTable  
	PIVOT  
	(  
	  MAX(DeltaValue)--There should only be one value, but we need MAX, MIN, SUM, COUNT or something.
	  FOR DeltaSystemName IN ([Attendance], [Punctuality], [Progress], [WorkOutstanding], 
								[LateStart], [ExceptionalEntry], [StudentSurvey], [WorkPlacement], 
								[Enrichment], [Comments], [Behavioural], [EmployabilitySkills],
								[User1], [User2], [User3], [User4],
								[User5], [User6], [User7], [User8])  --we need to pull out all 20!
	) AS PivotTable

	
SELECT 
	OGP_StudentID,
	[Attendance] AS [Attendance_Score], 
	[Punctuality] AS [Punctuality_Score], 
	[Progress] AS [Progress_Score] ,
	[WorkOutstanding] AS [WorkOutstanding_Score],
	[LateStart] AS [LateStart_Score],
	[ExceptionalEntry] AS [ExceptionalEntry_Score],
	[StudentSurvey] AS [StudentSurvey_Score],
	[WorkPlacement] AS [WorkPlacement_Score],
	[Enrichment] AS [Enrichment_Score], 
	[Comments] AS [Comments_Score],
	[Behavioural] AS [Behavioural_Score],
	[EmployabilitySkills] AS [EmployabilitySkills_Score],
	[User1] AS [User1_Score],
	[User2] AS [User2_Score],
	[User3] AS [User3_Score],
	[User4] AS [User4_Score],
	[User5] AS [User5_Score],
	[User6] AS [User6_Score],
	[User7] AS [User7_Score],
	[User8] AS [User8_Score]

INTO #TempPivot_Score
FROM  
	(
	  SELECT 
		  Delta_Category.SystemName AS DeltaSystemName, 
		  #Temp_DeltaScore_ByCategory.AcademicYearID,
		  #Temp_DeltaScore_ByCategory.[OGP_StudentID], 
		  #Temp_DeltaScore_ByCategory.DeltaScore
	  FROM 
		#Temp_DeltaScore_ByCategory
		  LEFT JOIN Delta_Category
		  ON Delta_Category.ID = #Temp_DeltaScore_ByCategory.DeltaCategoryID
	) AS SourceTable  
	PIVOT  
	(  
	  MAX(DeltaScore)--There should only be one value, but we need MAX, MIN, SUM, COUNT or something.
	  FOR DeltaSystemName IN ([Attendance], [Punctuality], [Progress], [WorkOutstanding], 
								[LateStart], [ExceptionalEntry], [StudentSurvey], [WorkPlacement], 
								[Enrichment], [Comments], [Behavioural], [EmployabilitySkills],
								[User1], [User2], [User3], [User4],
								[User5], [User6], [User7], [User8])  --we need to pull out all 20!
	) AS PivotTable

SELECT 
	OGP_StudentID,
	[Attendance] AS [Attendance_ScoreTrend], 
	[Punctuality] AS [Punctuality_ScoreTrend], 
	[Progress] AS [Progress_ScoreTrend] ,
	[WorkOutstanding] AS [WorkOutstanding_ScoreTrend],
	[LateStart] AS [LateStart_ScoreTrend],
	[ExceptionalEntry] AS [ExceptionalEntry_ScoreTrend],
	[StudentSurvey] AS [StudentSurvey_ScoreTrend],
	[WorkPlacement] AS [WorkPlacement_ScoreTrend],
	[Enrichment] AS [Enrichment_ScoreTrend], 
	[Comments] AS [Comments_ScoreTrend],
	[Behavioural] AS [Behavioural_ScoreTrend],
	[EmployabilitySkills] AS [EmployabilitySkills_ScoreTrend],
	[User1] AS [User1_ScoreTrend],
	[User2] AS [User2_ScoreTrend],
	[User3] AS [User3_ScoreTrend],
	[User4] AS [User4_ScoreTrend],
	[User5] AS [User5_ScoreTrend],
	[User6] AS [User6_ScoreTrend],
	[User7] AS [User7_ScoreTrend],
	[User8] AS [User8_ScoreTrend]

INTO #TempPivot_ScoreTrend
FROM  
	(
	  SELECT 
		  Delta_Category.SystemName AS DeltaSystemName, 
		  #Temp_DeltaScore_ByCategory.AcademicYearID,
		  #Temp_DeltaScore_ByCategory.[OGP_StudentID], 
		  #Temp_DeltaScore_ByCategory.DeltaScore_Trend
	  FROM 
		#Temp_DeltaScore_ByCategory
		  LEFT JOIN Delta_Category
		  ON Delta_Category.ID = #Temp_DeltaScore_ByCategory.DeltaCategoryID
	) AS SourceTable  
	PIVOT  
	(  
	  MAX(DeltaScore_Trend)--There should only be one value, but we need MAX, MIN, SUM, COUNT or something.
	  FOR DeltaSystemName IN ([Attendance], [Punctuality], [Progress], [WorkOutstanding], 
								[LateStart], [ExceptionalEntry], [StudentSurvey], [WorkPlacement], 
								[Enrichment], [Comments], [Behavioural], [EmployabilitySkills],
								[User1], [User2], [User3], [User4],
								[User5], [User6], [User7], [User8])  --we need to pull out all 20!
	) AS PivotTable

SELECT
	OGP_Student.ID,
	OGP_Student.StudentRef,
	OGP_Student.Forenames,
	OGP_Student.Surname,
	OGP_Student.DOB,
	(SELECT StudentCompletionID FROM #TempStudentCompletion WHERE #TempStudentCompletion.OGP_StudentID = OGP_Student.ID) AS CompletionID,
	(SELECT Completion.[Description] FROM #TempStudentCompletion INNER JOIN Completion ON #TempStudentCompletion.StudentCompletionID = Completion.ID WHERE #TempStudentCompletion.OGP_StudentID = OGP_Student.ID) AS CompletionStatus,
	StudentFlagValue1, StudentFlagValue2, StudentFlagValue3, StudentFlagValue4, StudentFlagValue5, StudentFlagValue6, 
	StudentFlagValue7, StudentFlagValue8, StudentFlagValue9, StudentFlagValue10, StudentFlagValue11, StudentFlagValue12,
	StudentFlagValue13, StudentFlagValue14, StudentFlagValue15, StudentFlagValue16, StudentFlagValue17, StudentFlagValue18,
	#Temp_DeltaScore_Overall.DeltaScore_Overall,
	#Temp_DeltaScore_Overall.DeltaScore_Overall_Trend,
	#Temp_DeltaScore_Overall.Latest_Data_Date,
	#Temp_DeltaScore_Overall.Latest_Data_Week,
	#TempPivot_Value.[Attendance_Value], [Punctuality_Value], [Progress_Value], #TempPivot_Value.[WorkOutstanding_Value],
	#TempPivot_Value.[LateStart_Value], #TempPivot_Value.[ExceptionalEntry_Value], #TempPivot_Value.[StudentSurvey_Value], #TempPivot_Value.[WorkPlacement_Value],
	#TempPivot_Value.[Enrichment_Value], #TempPivot_Value.[Comments_Value], #TempPivot_Value.[Behavioural_Value], #TempPivot_Value.[EmployabilitySkills_Value],
	#TempPivot_Value.[User1_Value], #TempPivot_Value.[User2_Value], #TempPivot_Value.[User3_Value], #TempPivot_Value.[User4_Value],
	#TempPivot_Value.[User5_Value], #TempPivot_Value.[User6_Value], #TempPivot_Value.[User7_Value], #TempPivot_Value.[User8_Value],

	#TempPivot_Score.[Attendance_Score], #TempPivot_Score.[Punctuality_Score], #TempPivot_Score.[Progress_Score], #TempPivot_Score.[WorkOutstanding_Score], --we need to pull out all 20!
	#TempPivot_Score.[LateStart_Score], #TempPivot_Score.[ExceptionalEntry_Score], #TempPivot_Score.[StudentSurvey_Score], #TempPivot_Score.[WorkPlacement_Score],
	#TempPivot_Score.[Enrichment_Score], #TempPivot_Score.[Comments_Score], #TempPivot_Score.[Behavioural_Score], #TempPivot_Score.[EmployabilitySkills_Score],
	#TempPivot_Score.[User1_Score], #TempPivot_Score.[User2_Score], #TempPivot_Score.[User3_Score], #TempPivot_Score.[User4_Score],
	#TempPivot_Score.[User5_Score], #TempPivot_Score.[User6_Score], #TempPivot_Score.[User7_Score], #TempPivot_Score.[User8_Score],

	#TempPivot_ScoreTrend.[Attendance_ScoreTrend], #TempPivot_ScoreTrend.[Punctuality_ScoreTrend], #TempPivot_ScoreTrend.[Progress_ScoreTrend], #TempPivot_ScoreTrend.[WorkOutstanding_ScoreTrend],
	#TempPivot_ScoreTrend.[LateStart_ScoreTrend], #TempPivot_ScoreTrend.[ExceptionalEntry_ScoreTrend], #TempPivot_ScoreTrend.[StudentSurvey_ScoreTrend], #TempPivot_ScoreTrend.[WorkPlacement_ScoreTrend],
	#TempPivot_ScoreTrend.[Enrichment_ScoreTrend], #TempPivot_ScoreTrend.[Comments_ScoreTrend], #TempPivot_ScoreTrend.[Behavioural_ScoreTrend], #TempPivot_ScoreTrend.[EmployabilitySkills_ScoreTrend],
	#TempPivot_ScoreTrend.[User1_ScoreTrend], #TempPivot_ScoreTrend.[User2_ScoreTrend], #TempPivot_ScoreTrend.[User3_ScoreTrend], #TempPivot_ScoreTrend.[User4_ScoreTrend],
	#TempPivot_ScoreTrend.[User5_ScoreTrend], #TempPivot_ScoreTrend.[User6_ScoreTrend], #TempPivot_ScoreTrend.[User7_ScoreTrend], #TempPivot_ScoreTrend.[User8_ScoreTrend],

	CASE WHEN Delta_Watchlist.OGP_StudentID = OGP_Student.ID THEN 1 ELSE 0 END AS IsInWatchlist

FROM
	#Temp_DeltaScore_Overall
	INNER JOIN OGP_Student
	ON #Temp_DeltaScore_Overall.OGP_StudentID = OGP_Student.ID
		INNER JOIN #TempPivot_Value
		ON #Temp_DeltaScore_Overall.OGP_StudentID = #TempPivot_Value.OGP_StudentID
			INNER JOIN #TempPivot_Score
			ON #Temp_DeltaScore_Overall.OGP_StudentID = #TempPivot_Score.OGP_StudentID 
				INNER JOIN #TempPivot_ScoreTrend
				ON #Temp_DeltaScore_Overall.OGP_StudentID = #TempPivot_ScoreTrend.OGP_StudentID  
					LEFT JOIN Delta_Student_InformationFlag
					ON Delta_Student_InformationFlag.OGP_StudentID = #Temp_DeltaScore_Overall.OGP_StudentID
						LEFT JOIN Delta_Watchlist
						ON OGP_Student.ID = Delta_Watchlist.OGP_StudentID
						AND Delta_Watchlist.StaffID = @StaffID
ORDER BY 4,3,2 -- Surname,Forename,StudentRef

GO

